Conditional Formatting with Select Case

[Guest]

I've tried using the following code or more than three conditional formats, but it doesn't work.

basically i want the background color to show red, green, yellow, or blue if the test in a cell says the same thing

Red text would = red background

any hints you can give me would be much apprepriated - Thanks

***** - five stars to anyone willing to give some feedback


Private Sub Worksheet_Calculate()
Dim oCell As Range
For Each oCell In Range("BF261:BF276")
Select Case oCell.Value
Case Is = Red
oCell.Interior.Pattern = xlColorIndexNone
oCell.Interior.PatternColorIndex = 3
Case Is = Blue
oCell.Interior.Pattern = xlColorIndexNone
oCell.Interior.PatternColorIndex = 5
Case Is = Green
oCell.Interior.Pattern = xlColorIndexNone
oCell.Interior.PatternColorIndex = 3
Case Is = Amber
oCell.Interior.Pattern = xlColorIndexNone
oCell.Interior.PatternColorIndex = 6
Case Is = Complete
oCell.Interior.Pattern = xlColorIndexNone
oCell.Interior.PatternColorIndex = 39
End Select
Next oCell
End Sub

 

[Dave Peterson]

The text in the cell actually contains the letters R-E-D?

If yes, try changing this:

Select Case oCell.Value
Case Is = Red
to:
Select Case lcase(oCell.Value)
Case Is = "red"

(and wrap all the other "color words" in double quotes and make them lowercase.
<well, unless case is important to you.>)