Originally Posted by
Paleo
I think this would be the best solution till now.
Sorry Paleo, inadequate testing. I had I2 selected on my tests.
The problem is caused by the realative adjustment of formulas by Excel, a very neat function, but it can catch you out sometimes.. This means that if you are in cell H3 when the conditional format is set, as that is one row and one column off the relative address in the formula, I2, it will adjust that realative address by one row and one column, and give J1. Thus the conditional formatting will be testing the incorrect cells.
The solution is not to select the range as suggestsed elsewhere (always cast doubt on anyone who says you need to select in VBA), but to allow for this adjustment in the code by using the activecell.
Public Sub CFRange()
Dim rng As Range
Dim oFormat As FormatCondition
Set rng = Range("I2:I10")
With rng
.FormatConditions.Delete
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & "=SMALL($I$2:$I$10,1)")
oFormat.Interior.ColorIndex = 50
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & "=SMALL($I$2:$I$10,2)")
oFormat.Interior.ColorIndex = 6
Set oFormat = .FormatConditions.Add(Type:=xlExpression, _
Formula1:="=" & ActiveCell.Address(False, False) & "=SMALL($I$2:$I$10,3)")
oFormat.Interior.ColorIndex = 7
End With
End Sub