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

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" wrote:

> 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, thanks for your help. It works good, and you've also shown the way to
manipulate the font color. I'll tinker with it.

Your welcome and thanks for the feedback

"Max" wrote:

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