CF sheet sub modifications

[Max]

Seeking help to modify the sub below to conditionally format cols A to T
based on numbers in key col U (in U2:U100). Currently it CF's only the range
U2:U100. And for viewing clarity, how to add lines to the sub so that the
font color will "contrast" with the fill color (eg black font for light
fills, white for dark fills). Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("U2:U100")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

 

[Mike H]

Max,

Try this. With regard to font colour that's largely subjective so I'll leave
it to you to pick the colour you want. All are currently set to black.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("U2:U100")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
fcolor = 1
Case 6 To 10
icolor = 12
fcolor = 1
Case 11 To 15
icolor = 7
fcolor = 1
Case 16 To 20
icolor = 53
fcolor = 1
Case 21 To 25
icolor = 15
fcolor = 1
Case 26 To 30
icolor = 42
fcolor = 1
Case Else
End Select
With Target
.Offset(0, -20).Resize(1, 20).Interior.ColorIndex = icolor
.Offset(0, -20).Resize(1, 20).Font.ColorIndex = fcolor
End With
End If
End Sub


Mike

 

[Max]

Mike, thanks for your help. It works good, and you've also shown the way to
manipulate the font color. I'll tinker with it.

 

[Mike H]

Your welcome and thanks for the feedback