2 5-range color codes on the same sheet

[Libby]

This code works great for 1 row, but what do I do for
another range on the same worksheet with different values?
I tried changing the name to public and adding the 2nd
range as another IF routine, but only the 2nd values work.
(see below) Please help one more time - I promise to quit
asking for a long time.

THANK YOU

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range
("A1:G55,I55,K55,M55,O55,Q55,S55,U55,W55,Y55,AA55,AC55,AE55
")) Is Nothing Then
With Target
Select Case True
Case .Value >= 1755 And .Value < 1853:
.Interior.ColorIndex = 6 'Yellow
Case .Value >= 1853 And .Value < 2048:
.Interior.ColorIndex = 10 'Green
Case .Value >= 2048 And .Value <= 2145:
.Interior.ColorIndex = 6 'Yellow
Case Else:
.Interior.ColorIndex = 3 'Red
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

Public Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range
("A1:G55,I55,K55,M55,O55,Q55,S55,U55,W55,Y55,AA55,AC55,AE55
")) Is Nothing Then
With Target
Select Case True
Case .Value >= 1755 And .Value < 1853:
.Interior.ColorIndex = 6 'Yellow
Case .Value >= 1853 And .Value < 2048:
.Interior.ColorIndex = 10 'Green
Case .Value >= 2048 And .Value <= 2145:
.Interior.ColorIndex = 6 'Yellow
Case Else:
.Interior.ColorIndex = 3 'Red
End Select
End With
End If
Application.EnableEvents = False
On Error GoTo ws_exit

If Not Intersect(Target, Range
("A1:G56,I56,K56,M56,O56,Q56,S56,U56,W56,Y56,AA56,AC56,AE56
")) Is Nothing Then
With Target
Select Case True
Case .Value >= 1858 And .Value < 1962:
.Interior.ColorIndex = 6 'Yellow
Case .Value >= 1962 And .Value < 2168:
.Interior.ColorIndex = 10 'Green
Case .Value >= 2168 And .Value <= 2272:
.Interior.ColorIndex = 6 'Yellow
Case Else:
.Interior.ColorIndex = 3 'Red
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

Libby,

Try this (untested I am afraid)

Public Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range
("A1:G55,I55,K55,M55,O55,Q55,S55,U55,W55,Y55,AA55,AC55,AE55
")) Is Nothing Then
With Target
Select Case True
Case .Value >= 1755 And .Value < 1853:
.Interior.ColorIndex = 6 'Yellow
Case .Value >= 1853 And .Value < 2048:
.Interior.ColorIndex = 10 'Green
Case .Value >= 2048 And .Value <= 2145:
.Interior.ColorIndex = 6 'Yellow
Case Else:
.Interior.ColorIndex = 3 'Red
End Select
End With
ElseIf Not Intersect(Target, Range
("A1:G56,I56,K56,M56,O56,Q56,S56,U56,W56,Y56,AA56,AC56,AE56
")) Is Nothing Then
With Target
Select Case True
Case .Value >= 1858 And .Value < 1962:
.Interior.ColorIndex = 6 'Yellow
Case .Value >= 1962 And .Value < 2168:
.Interior.ColorIndex = 10 'Green
Case .Value >= 2168 And .Value <= 2272:
.Interior.ColorIndex = 6 'Yellow
Case Else:
.Interior.ColorIndex = 3 'Red
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

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

 

[Libby]

Thank you, thank you, thank you. It works great!

-----Original Message-----
Libby,

Try this (untested I am afraid)

Public Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range
("A1:G55,I55,K55,M55,O55,Q55,S55,U55,W55,Y55,AA55,AC55,AE55
")) Is Nothing Then
With Target
Select Case True
Case .Value >= 1755 And .Value < 1853:
.Interior.ColorIndex = 6 'Yellow
Case .Value >= 1853 And .Value < 2048:
.Interior.ColorIndex = 10 'Green
Case .Value >= 2048 And .Value <= 2145:
.Interior.ColorIndex = 6 'Yellow
Case Else:
.Interior.ColorIndex = 3 'Red
End Select
End With
ElseIf Not Intersect(Target, Range
("A1:G56,I56,K56,M56,O56,Q56,S56,U56,W56,Y56,AA56,AC56,AE56
")) Is Nothing Then
With Target
Select Case True
Case .Value >= 1858 And .Value < 1962:
.Interior.ColorIndex = 6 'Yellow
Case .Value >= 1962 And .Value < 2168:
.Interior.ColorIndex = 10 'Green
Case .Value >= 2168 And .Value <= 2272:
.Interior.ColorIndex = 6 'Yellow
Case Else:
.Interior.ColorIndex = 3 'Red
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

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




.