Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CF sheet sub modifications
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CF sheet sub modifications
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CF sheet sub modifications
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CF sheet sub modifications
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Modifications | Excel Worksheet Functions | |||
modifications on the code | Excel Programming | |||
webbrowser - excel - modifications | Excel Discussion (Misc queries) | |||
How do I check modifications of file on server | Excel Programming | |||
Modifications to Permutation Macro | Excel Programming |