Making a macro run on a "paste special-values"

[CO Engr]

I have a worksheet that receives a "paste special - values" whenever text is entered into another sheet. On this receiving sheet, I have a macro that automatically turns a cell a certain color when the proper text is entered. When I type the text, or paste one cell, the macro works fine. But if I 'paste special - values' for a whole column the macro doesn't run. If I then click in a cell with the specified text, the cell then changes color. Is there a way to modify this macro so that the user doesn't have to click in each cell to get the background color to change?

Thanks for your help, here is the code that I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Set isect = Application.Intersect(Range("A1:CC1000"), Range(Target.Address))
If Not isect Is Nothing Then
ColorCode = Right(Trim(Target.Value), 3)
Select Case ColorCode
Case "CAR": Target.Interior.ColorIndex = 40
Case "CA": Target.Interior.ColorIndex = 34
Case "CD": Target.Interior.ColorIndex = 35
Case "DD": Target.Interior.ColorIndex = 37
Case "SD": Target.Interior.ColorIndex = 38
Case "CON": Target.Interior.ColorIndex = 39
Case Else
Target.Interior.ColorIndex = 0
End Select
End If

 

[Tom Urtis]

When I type the text, or paste one cell, the macro works fine. But if I 'paste special - values' for a whole column the macro doesn't run.

Well, why do you have this line in there then?
If Target.Count > 1 Then Exit Sub

Take it out and see what happens when you paste an entire column.

Also, make sure the activity taking place, which you expect to result in a triggerable event, is indeed taking place within A1:CC1000.

 

[CO Engr]

I took that line out and then pasted an entire column and I got an error message "Run time error 13 - Type mismatch" How should I code this so that I can change more than one cell and still have the routine run?

Thanks for your help.

 

[Tom Urtis]

If you take a look at your code, you'll notice that the crux of the color index change is based on one cell changing. That is fine if you are changing one cell at a time. But, if you change your activity plans, by pasting in an entire column, then you need to adjust your code to loop through the new cells that have been changed, so they are evaluated and colored accordingly. The structure of the procedure as it is now served your first intention of changing one cell at a time, but it needs to be rewritten to identify the range being changed, and then loop through the individual cell objects to shade them. This would be a good candidate for conditional formatting if all you are transferring is values, but you have a half-dozen colors to deal with so VBA seems like the only alternative.