Title:Excel VBA bug creates incorrect conditional format cell references
Author:Mark Kiehl
Category:MS Excel
Sub CreateConditionalFmt()
'Mark Kiehl, 24Feb2009, www.SavyCode-Solutions.com

'Excel VBA bug creates incorrect conditional format cell references.
'
'Excel has a bug that creates incorrect cell references for conditional formats
'when the cells references contain relative references (A1:A5) rather than
'absolute references ($A$1:$A$5). The Microsoft knowledge base article 895562
'suggests a fix, but they do not explain it fully. Through trial & error I
'have discovered a complete solution to the problem.
'See article: http://support.microsoft.com/kb/895562

'Setup the example (omit for your application)
ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = 500
ThisWorkbook.Worksheets("Sheet1").Range("A3").Value = 250
ThisWorkbook.Worksheets("Sheet1").Range("A4").Value = 550
ThisWorkbook.Worksheets("Sheet1").Range("A5").Value = 150
ThisWorkbook.Worksheets("Sheet1").Range("A6").Value = 600
ThisWorkbook.Worksheets("Sheet1").Range("A7").Value = 350

'In the example below I have set up the example to use Cells(r,c) format
'so that you can see how to program a number of columns with the solution
'by replacing the cell column numbers with a variable.
'I have also intentionally used a conditional format formulat that requires
'both a relative and absolute reference over a multi row range.
'Multiple conditional formats and application of the same (relative)
'conditional format over a range is demonstrated.

'Add a conditional format to the cell A3
With ThisWorkbook.Worksheets("Sheet1").Range(Cells(3, 1), Cells(3, 1))
'Delete any existing conditional formats
.FormatConditions.Delete
'*** The 1st "trick" is to select the cell to receive the conditional
'formatting prior to adding the conditional format.
.Select
'Below is the formula we want as it would be entered manually as a formula
'through the Excel conditional format interface.
'.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($A3>$A$1,TRUE,FALSE)"
.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($" & ColumnLetter(1) & _
"3>$" & ColumnLetter(1) & "$1,TRUE,FALSE)"
.FormatConditions(1).Font.ColorIndex = 5 '1=black,3=red,5=blue,xlAutomatic
.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($" & ColumnLetter(1) & _
"3<$" & ColumnLetter(1) & "$1,TRUE,FALSE)"
'Add a second conditional format
'.FormatConditions.Add Type:=xlExpression, Formula1:="=IF($A3<$A$1,TRUE,FALSE)"
.FormatConditions(2).Font.ColorIndex = 3 '1=black,3=red,5=blue,xlAutomatic
End With

'Apply the conditional format defined in cell A3 to a range of cells below it
'This is a fast approach to format a large number of rows with a similar formula.
'Note that the relative portion of the conditional format range is preserved.

'Select the range of cells you wish to have the same (relative) conditional
'format as in cell A3
With ThisWorkbook.Worksheets("Sheet1").Range(Cells(4, 1), Cells(7, 1))
.FormatConditions.Delete
'Assign the conditional format formula for cells A4:A7 to the formula for cell A3
.FormatConditions.Add Type:=xlExpression, Formula1:=ThisWorkbook.Worksheets("Sheet1"). _
Range(Cells(3, 1), Cells(3, 1)).FormatConditions.Item(1).Formula1
.FormatConditions(1).Font.ColorIndex = 5 'blue
.FormatConditions.Add Type:=xlExpression, Formula1:=ThisWorkbook.Worksheets("Sheet1"). _
Range(Cells(3, 1), Cells(3, 1)).FormatConditions.Item(2).Formula1
.FormatConditions(2).Font.ColorIndex = 3 'red
End With
Beep
End Sub

Function ColumnLetter(ByVal colNum As Long) As String
'converts numerical indices into Excel-style "A1:C1" string notation.
'alternative at: http://support.microsoft.com/kb/q198144/
Dim i As Long, x As Long
For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
x = (26 ^ (i + 1) - 1) / 25 - 1
If colNum > x Then
ColumnLetter = ColumnLetter & Chr(((colNum - x - 1) \ 26 ^ i) Mod 26 + 65)
End If
Next i
End Function