How can I make a cell change colour according to the result of its LOOKUP
formula?
I have put in the following code, but this will only work on cells that
don't have the formula, whereas I need the cells to change colour according
to the result of the formula.
Hope this makes sense!

Dim color As Integer

If Not Intersect(Target, Range("c1:c10")) Is Nothing Then
Select Case Target
Case 3 To 17
color = 3
Case 19
color = 45
Case 21
color = 4
Case 23 To 35
color = 33
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = color
End If