Solved: Changing Cell Fill Color Depending On Its Contents

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

Gary

Could you give some more information on your conditions?

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:

[vba]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[/vba]

Just add a case for each scenario.

HTH,

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.
[VBA]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
[/VBA]

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

Thanks
Gary

[VBA]
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
[/VBA]

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

Once again you guys & gals came through. Problem solved.
Thanks
Gary

Just for fun, here's another way to skin it:

[vba]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[/vba] [/left]

I thought of that Ken, but of course if you type "PLACE" or "VICTORY" in a cell they will also be coloured ...

Good point, Anne! :)