The problem is that when you make multiple changes, it is NOT calling the Worksheet_Change event multiple times, it passes an ARRAY of changed cells. But when only one cell is changed, it passes a single range object.
try adapting something like: '======================================================== Private Sub Worksheet_Change(ByVal Target As Range) Dim x, msg If Target.Count = 1 Then MsgBox Target.Row & " : " & Target.Value Else msg = "Changes:" For x = 1 To Target.Count msg = msg & Chr(13) & Target(x).Row & " : " & Target(x).Value Next x MsgBox msg End If End Sub hope this helps, paul ________________________________ From: Johan <riesb...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Thursday, June 11, 2009 8:10:09 AM Subject: $$Excel-Macros$$ Change data on different sheet I'm writing a VB script which should execute every time I update a cell on a certain sheet. The script should check my value and based on this change the formatting on the same cell in all other sheet. I got this working with the following code: Private Sub Worksheet_Change(ByVal Target As Range) For cntr = 4 To Sheets.Count With Sheets(cntr).Cells(Target.Row, Target.Column).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.149998474074526 .PatternTintAndShade = 0 End With Next cntr end sub However, this does not work if I change more than one cell at a time, i.e. it generates an error if a drag a value through several cell or if I cut and paste into more than one cell. I assume this has to do with the fact that I am using the cells properties combined with the target.cell and target.column but how do I write this using the range routines or any other function. Regards Johan Riesbeck --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
