PDA

View Full Version : Solved: Changing Cell Fill Color Depending On Its Contents



zoom38
01-17-2006, 10:02 PM
I have a worksheet in which I need b8:o31 to automatically change the fill color based on its contents. I tried conditional format but that only allows for 3 conditions. I will need approximately 70 conditions that need to be addressed. If I can use wildcard characters I could narrow it down to about 10 conditions. Can wildcard characters be used in a conditional format? I tried but with no success. I did a search on VBA Express and on the internet but could not find what I need. Can you guys lead me in the right direction.

Thanks
Gary

Norie
01-17-2006, 11:14 PM
Gary

Could you give some more information on your conditions?

Ken Puls
01-18-2006, 12:17 AM
Hi Gary,

You could try this. The code must go in a worksheet module. Find the sheet you want this to run on, right click it, click view code, and paste this in the module that shows up:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case Is = 1
Target.Interior.ColorIndex = 6
Case Is = 2
Target.Interior.ColorIndex = 23
Case Is = "Hello"
Target.Interior.ColorIndex = 35
Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select

End Sub

Just add a case for each scenario.

HTH,

zoom38
01-18-2006, 06:08 PM
Ken that worked nicely but it is quite long. Is there a way to shorten it up? Can I use wildcard characters? If I could that would shorten it up significantly. If you look at the code you will understand what I mean.
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case Is = "V.5"
Target.Interior.ColorIndex = 35
Case Is = "V1"
Target.Interior.ColorIndex = 35
Case Is = "V1.5"
Target.Interior.ColorIndex = 35
Case Is = "V2"
Target.Interior.ColorIndex = 35
Case Is = "V2.5"
Target.Interior.ColorIndex = 35
Case Is = "V3"
Target.Interior.ColorIndex = 35
Case Is = "V3.5"
Target.Interior.ColorIndex = 35
Case Is = "V4"
Target.Interior.ColorIndex = 35
Case Is = "V4.5"
Target.Interior.ColorIndex = 35
Case Is = "V5"
Target.Interior.ColorIndex = 35
Case Is = "V5.5"
Target.Interior.ColorIndex = 35
Case Is = "V6"
Target.Interior.ColorIndex = 35
Case Is = "V6.5"
Target.Interior.ColorIndex = 35
Case Is = "V7"
Target.Interior.ColorIndex = 35
Case Is = "V7.5"
Target.Interior.ColorIndex = 35
Case Is = "V8"
Target.Interior.ColorIndex = 35
Case Is = "V8.5"
Target.Interior.ColorIndex = 35
Case Is = "V9"
Target.Interior.ColorIndex = 35
Case Is = "V9.5"
Target.Interior.ColorIndex = 35
Case Is = "V10"
Target.Interior.ColorIndex = 35
Case Is = "V10.5"
Target.Interior.ColorIndex = 35
Case Is = "V11"
Target.Interior.ColorIndex = 35
Case Is = "V11.5"
Target.Interior.ColorIndex = 35
Case Is = "V12"
Target.Interior.ColorIndex = 35
Case Is = "PL.5"
Target.Interior.ColorIndex = 34
Case Is = "PL1"
Target.Interior.ColorIndex = 34
Case Is = "PL1.5"
Target.Interior.ColorIndex = 34
Case Is = "PL2"
Target.Interior.ColorIndex = 34
Case Is = "PL2.5"
Target.Interior.ColorIndex = 34
Case Is = "PL3"
Target.Interior.ColorIndex = 34
Case Is = "PL3.5"
Target.Interior.ColorIndex = 34
Case Is = "PL4"
Target.Interior.ColorIndex = 34
Case Is = "PL4.5"
Target.Interior.ColorIndex = 34
Case Is = "PL5"
Target.Interior.ColorIndex = 34
Case Is = "PL5.5"
Target.Interior.ColorIndex = 34
Case Is = "PL6"
Target.Interior.ColorIndex = 34
Case Is = "PL6.5"
Target.Interior.ColorIndex = 34
Case Is = "PL7"
Target.Interior.ColorIndex = 34
Case Is = "PL7.5"
Target.Interior.ColorIndex = 34
Case Is = "PL8"
Target.Interior.ColorIndex = 34
Case Is = "PL8.5"
Target.Interior.ColorIndex = 34
Case Is = "PL9"
Target.Interior.ColorIndex = 34
Case Is = "PL9.5"
Target.Interior.ColorIndex = 34
Case Is = "PL10"
Target.Interior.ColorIndex = 34
Case Is = "PL10.5"
Target.Interior.ColorIndex = 34
Case Is = "PL11"
Target.Interior.ColorIndex = 34
Case Is = "PL11.5"
Target.Interior.ColorIndex = 34
Case Is = "PL12"
Target.Interior.ColorIndex = 34
Case Is = "SL.5"
Target.Interior.ColorIndex = 36
Case Is = "SL1"
Target.Interior.ColorIndex = 36
Case Is = "SL1.5"
Target.Interior.ColorIndex = 36
Case Is = "SL2"
Target.Interior.ColorIndex = 36
Case Is = "SL2.5"
Target.Interior.ColorIndex = 36
Case Is = "SL3"
Target.Interior.ColorIndex = 36
Case Is = "SL3.5"
Target.Interior.ColorIndex = 36
Case Is = "SL4"
Target.Interior.ColorIndex = 36
Case Is = "SL4.5"
Target.Interior.ColorIndex = 36
Case Is = "SL5"
Target.Interior.ColorIndex = 36
Case Is = "SL5.5"
Target.Interior.ColorIndex = 36
Case Is = "SL6"
Target.Interior.ColorIndex = 36
Case Is = "SL6.5"
Target.Interior.ColorIndex = 36
Case Is = "SL7"
Target.Interior.ColorIndex = 36
Case Is = "SL7.5"
Target.Interior.ColorIndex = 36
Case Is = "SL8"
Target.Interior.ColorIndex = 36
Case Is = "SL8.5"
Target.Interior.ColorIndex = 36
Case Is = "SL9"
Target.Interior.ColorIndex = 36
Case Is = "SL9.5"
Target.Interior.ColorIndex = 36
Case Is = "SL10"
Target.Interior.ColorIndex = 36
Case Is = "SL10.5"
Target.Interior.ColorIndex = 36
Case Is = "SL11"
Target.Interior.ColorIndex = 36
Case Is = "SL11.5"
Target.Interior.ColorIndex = 36
Case Is = "SL12"
Target.Interior.ColorIndex = 36
Case Is = "FS.5"
Target.Interior.ColorIndex = 36
Case Is = "FS1"
Target.Interior.ColorIndex = 36
Case Is = "FS1.5"
Target.Interior.ColorIndex = 36
Case Is = "FS2"
Target.Interior.ColorIndex = 36
Case Is = "FS2.5"
Target.Interior.ColorIndex = 36
Case Is = "FS3"
Target.Interior.ColorIndex = 36
Case Is = "FS3.5"
Target.Interior.ColorIndex = 36
Case Is = "FS4"
Target.Interior.ColorIndex = 36
Case Is = "FS4.5"
Target.Interior.ColorIndex = 36
Case Is = "FS5"
Target.Interior.ColorIndex = 36
Case Is = "FS5.5"
Target.Interior.ColorIndex = 36
Case Is = "FS6"
Target.Interior.ColorIndex = 36
Case Is = "FS6.5"
Target.Interior.ColorIndex = 36
Case Is = "FS7"
Target.Interior.ColorIndex = 36
Case Is = "FS7.5"
Target.Interior.ColorIndex = 36
Case Is = "FS8"
Target.Interior.ColorIndex = 36
Case Is = "FS8.5"
Target.Interior.ColorIndex = 36
Case Is = "FS9"
Target.Interior.ColorIndex = 36
Case Is = "FS9.5"
Target.Interior.ColorIndex = 36
Case Is = "FS10"
Target.Interior.ColorIndex = 36
Case Is = "FS10.5"
Target.Interior.ColorIndex = 36
Case Is = "FS11"
Target.Interior.ColorIndex = 36
Case Is = "FS11.5"
Target.Interior.ColorIndex = 36
Case Is = "FS12"
Target.Interior.ColorIndex = 36
Case Is = "ML.5"
Target.Interior.ColorIndex = 24
Case Is = "ML1"
Target.Interior.ColorIndex = 24
Case Is = "ML1.5"
Target.Interior.ColorIndex = 24
Case Is = "ML2"
Target.Interior.ColorIndex = 24
Case Is = "ML2.5"
Target.Interior.ColorIndex = 24
Case Is = "ML3"
Target.Interior.ColorIndex = 24
Case Is = "ML3.5"
Target.Interior.ColorIndex = 24
Case Is = "ML4"
Target.Interior.ColorIndex = 24
Case Is = "ML4.5"
Target.Interior.ColorIndex = 24
Case Is = "ML5"
Target.Interior.ColorIndex = 24
Case Is = "ML5.5"
Target.Interior.ColorIndex = 24
Case Is = "ML6"
Target.Interior.ColorIndex = 24
Case Is = "ML6.5"
Target.Interior.ColorIndex = 24
Case Is = "ML7"
Target.Interior.ColorIndex = 24
Case Is = "ML7.5"
Target.Interior.ColorIndex = 24
Case Is = "ML8"
Target.Interior.ColorIndex = 24
Case Is = "ML8.5"
Target.Interior.ColorIndex = 24
Case Is = "ML9"
Target.Interior.ColorIndex = 24
Case Is = "ML9.5"
Target.Interior.ColorIndex = 24
Case Is = "ML10"
Target.Interior.ColorIndex = 24
Case Is = "ML10.5"
Target.Interior.ColorIndex = 24
Case Is = "ML11"
Target.Interior.ColorIndex = 24
Case Is = "ML11.5"
Target.Interior.ColorIndex = 24
Case Is = "ML12"
Target.Interior.ColorIndex = 24
Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select

End Sub


Also how do I make it so it will work on the entire workbook not just the worksheet?

Thanks
Gary

geekgirlau
01-18-2006, 09:32 PM
Select Case Target.Value
Case "V.5", "V1" To "V999"
Target.Interior.ColorIndex = 35

Case "PL.5", "PL1" To "PL999"
Target.Interior.ColorIndex = 34

Case "SL.5", "SL1" To "SL999", "FS.5", "FS1" To "FS999"
Target.Interior.ColorIndex = 36

Case Is = "ML.5", "ML1" To "ML999"
Target.Interior.ColorIndex = 24

Case Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End Select


Make it part of the workbook SheetChange event if you want it to occur on all sheets.

zoom38
01-19-2006, 04:27 PM
Once again you guys & gals came through. Problem solved.
Thanks
Gary

Ken Puls
01-20-2006, 04:42 PM
Just for fun, here's another way to skin it:

Select Case Left(Target.Value,2)

Case "PL"
Target.Interior.ColorIndex = 34

Case "SL", "FS"
Target.Interior.ColorIndex = 36

Case Is = "ML"
Target.Interior.ColorIndex = 24

Case Else
If Left(Target.Value)="V" Then
Target.Interior.ColorIndex = 35
Else
'No conditions met, so make it normal
Target.Interior.ColorIndex = -4142
End if
End Select [/left]

geekgirlau
01-21-2006, 04:05 PM
I thought of that Ken, but of course if you type "PLACE" or "VICTORY" in a cell they will also be coloured ...

Ken Puls
01-23-2006, 03:53 PM
Good point, Anne! :)