Color Index on Multiple Rows

[DBarnard]

I'm having difficulty with the following code. It checks a row (B co
through end column) and any mismatching information, it marks as a re
background. This code works fine if I manually type in information
but does not work if I paste information in (single or multipl
entries). The weird thing is, the debugging statements still writ
either way. Any ideas?

Here's the code:
Public Sub Worksheet_Change(ByVal Target As Range)
Dim A_Range As Range, rngCheck As Range
Dim EndCol As Long, EndRow As Long, OffsetRow As Long, RowNum A
Long, tempRow As Long
Set A_Range = Excel.Union(Range("A7:A38"), Range("A42:A57")
Range("A62:A69"), Range("A75:A78"), Range("A84:A85"), Range("A91"))
If Not Intersect(Target, A_Range) Is Nothing Then
EndCol = UsedRange.Columns.Count
Do While Cells(1, EndCol) = ""
EndCol = EndCol - 1
Loop
Debug.Print "End Col = " + CStr(EndCol)
EndRow = Target.Rows.Count
Debug.Print "End Row = " + CStr(EndRow)
OffsetRow = Target.Offset.row - 1
Debug.Print "Offset = " + CStr(OffsetRow)
For RowNum = 1 To EndRow
If Target(RowNum, 1).Value <> "" Then
tempRow = RowNum + OffsetRow
For ndx = 2 To EndCol
If Cells(tempRow, ndx).Value <> Target(RowNum
1).Value Then
Cells(tempRow, ndx).Interior.ColorIndex = 3
Debug.Print CStr(tempRow) + "," + CStr(ndx)
"=Red"
End If
Next ndx
End If
Next RowNum
End If
End Su

 

[Bob Phillips]

Whilst I do not have the data that you have, a simple test worked fine for
me.

You say the debug statements work. Is that true for
Debug.Print CStr(tempRow) + "," + CStr(ndx) + "=Red"
if so, it is hard to see why the solour doesn't get set.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[DBarnard]

If you notice, the debug statement is just printing out what th
previous statement is doing

 

[Dave Peterson]

Your code changed a bunch of cells to red for me--if they didn't match the value
in column A.

Two guesses why it didn't work for you:

You have some other code that you didn't share.

It worked perfectly, but the color that had an index of 3 is White for this
workbook.

If you change that 3 to a different value, do you get something that looks like
it works?

 

[DBarnard]

Thanks for the reply. It works if I manually type the information in
but if I paste rows (1 or multiple), it doesn't work. I've looke
around and apparently Excel doesn't trigger the Change event when yo
paste data, but it's still executing the code (hence the debu
statements). I just wanted to know if anyone had any workarounds.

Thx again...

 

[Bob Phillips]

Those statements are not consistent. If it doesn't trigger the change event
then the debug code cannot be execute as it is part of the change event.

There is something here that is not being understood by any of us. Do you
want to send me your workbook and I will look at it (note my anti-spam
message).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)