I have been reading the code for conditinal formatting, so it's posible to have more than 3 rules (see below):
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) 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
This one is for numbers but I have text instead. I have different kind of text that I find with lookup. Now I want to color the cell according to the text that I find during the lookup.