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
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