Condition Formatting Code for text in cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook that consists of monthly calenders that uses single text
characters in cells that I want to automatically convert to a specific colour
when entered in a range in each worksheet. I am familiar with conditional
formatting but have more than three different formats. I need some basic vba
code to colour the cells that match the following entire cell contents:

Dog Purple
Cat Blue
M Red
O Yellow
V Green
 
Let's say the cells are in column A. Try the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A")
If Intersect(Target, r) Is Nothing Then Exit Sub
With Target
Select Case .Value
Case "Dog"
.Interior.ColorIndex = 39
Case "Cat"
.Interior.ColorIndex = 5
Case "M"
.Interior.ColorIndex = 3
Case "O"
.Interior.ColorIndex = 6
Case "V"
.Interior.ColorIndex = 10
End Select
End With
End Sub
 
Back
Top