Hi Everyone,
I have a major issue that I hope can be solved since I have spent about three days trying to figure this one out.
I have text in range1 ("A18:A300") but not in every cell. There are also numerical values in range2 ("G18:BB300") but not in every cell. As the values change in range1, I need to change the interior color of the cells in range2 that contain values. Also if I add a value to range2, then I need the cell to change to the appropriate color for the related value in range1. I wrote a code to do it one row at a time, but there has to be a better way. Any help would be greatly appreciated.
Randy
Here is the code I wrote for just one row:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Range1 As Range
Dim Range2 As Range
Dim Cell As Variant
Set Code = Range("A18:A18")
Set Track = Range("G18:BB18")
[tt]If Not Intersect(Target, Range1) Is Nothing Or _
Not Intersect(Target, Range2) Is Nothing Then
For Each Cell In Range2
If Range1 = "s" And Cell > 0 Then
Cell.Interior.ColorIndex = 34
ElseIf Range1 = "o" And Cell > 0 Then
Cell.Interior.ColorIndex = 50
ElseIf Range1 = "e" And Cell > 0 Then
Cell.Interior.ColorIndex = 40
ElseIf Range1 = "f" And Cell > 0 Then
Cell.Interior.ColorIndex = 41
ElseIf Range1 = "ee" And Cell > 0 Then
Cell.Interior.ColorIndex = 7
ElseIf Range1 = "p" And Cell > 0 Then
Cell.Interior.ColorIndex = 6
ElseIf Range1 = "" Or Cell = "" Then
Cell.Interior.ColorIndex = xlblank
End If
Next Cell
End If
End Sub[/tt]
I have a major issue that I hope can be solved since I have spent about three days trying to figure this one out.
I have text in range1 ("A18:A300") but not in every cell. There are also numerical values in range2 ("G18:BB300") but not in every cell. As the values change in range1, I need to change the interior color of the cells in range2 that contain values. Also if I add a value to range2, then I need the cell to change to the appropriate color for the related value in range1. I wrote a code to do it one row at a time, but there has to be a better way. Any help would be greatly appreciated.
Randy
Here is the code I wrote for just one row:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Range1 As Range
Dim Range2 As Range
Dim Cell As Variant
Set Code = Range("A18:A18")
Set Track = Range("G18:BB18")
[tt]If Not Intersect(Target, Range1) Is Nothing Or _
Not Intersect(Target, Range2) Is Nothing Then
For Each Cell In Range2
If Range1 = "s" And Cell > 0 Then
Cell.Interior.ColorIndex = 34
ElseIf Range1 = "o" And Cell > 0 Then
Cell.Interior.ColorIndex = 50
ElseIf Range1 = "e" And Cell > 0 Then
Cell.Interior.ColorIndex = 40
ElseIf Range1 = "f" And Cell > 0 Then
Cell.Interior.ColorIndex = 41
ElseIf Range1 = "ee" And Cell > 0 Then
Cell.Interior.ColorIndex = 7
ElseIf Range1 = "p" And Cell > 0 Then
Cell.Interior.ColorIndex = 6
ElseIf Range1 = "" Or Cell = "" Then
Cell.Interior.ColorIndex = xlblank
End If
Next Cell
End If
End Sub[/tt]