Excel VBA conditional formatting auto-change

Question

Question:
Is there anything that would cause the Formula1 parameter of the FormatConditions.Add method to change automatically, or to change from what is hard-coded in an Excel-VBA macro?
If so, where is the documentation for this behavior?

Description of Problem:
When applying the FormatConditions.Add method to a range, the formula does not match what is specified in the code.

My macro code assigns a formula to a variable named ConditionalRangeFormula. After running the macro the actual conditional formatting formula does not match ConditionalRangeFormula, and the row in the formula does not match the row that was specified in the code. See the "Details" section below for more info.

Hypothesis:
Note 1:
I've noticed that with a range, Excel will automatically "fit" a conditional formatting formula to match the specifics for each cell in a range. For example, in a worksheet with random numbers between 1 and 10 in column A:

I choose column A.
I add a conditional format to column A, with a formula "=IF(A1=2,1)". The cell font is formatted bold red if this formula is true.
Every cell in column A that contains "2" will be bold red, not just cell A1, even though the formula is just for A1.

Is it possible that in the background Excel is doing some changing of my formula in the code above, in an attempt to "guess" what the formula actually should be?

Note 2:
I don't think this is a result of using too many conditional formats for a range. In Microsoft's Excel developer notes for "FormatConditions.Add Method", there is a remark that "You cannot define more than three conditional formats for a range." However, I've successfully added more than three conditional formats with no changes (see details below). Also, I've tested with all other conditional formatting commented out (inactivated), so that only one conditional format is applied, with no changes.

Details:
I'm using Excel 2007 on a Win7 machine.

My code is a little more complex than the example given in the hypothesis above.

The conditional format function is designed to check if a cell in column "AP" is blank, then apply a red outline.

If I place a breakpoint at the With conditionalRange.FormatConditions _.add(xlExpression, , ConditionalRangeFormula) line, I can confirm ConditionalRangeFormula is correct ("=ISBLANK($AP1)"). However, after running, the conditional formatting formula for every cell in the specified range is "=ISBLANK($AP2)". This does what my code specifies.

Please note the working range (ConditionalRange is the code below) actually starts with row 2 of column AP, since row 1 is a header row. As an interesting note, if I make ConditionalRangeFormula "=ISBLANK($AP2)", the conditional formatting formula for every cell in the specified range is "=ISBLANK($AP3)". Notice how the row in the formula is +1 from what is hard coded, just as in the first situation described in the previous paragraph. Interesting behavior, but I can't find documentation for this.

Also, please note that there are four With...End With statements that apply conditional formatting to that cell, before the conditional formatting that gives problems is applied. Each of those first four statements use formulas that work as expected, so I've simplified those code blocks to make the overall code easier to follow. See "Note 2" under the Hypothesis section above for more details.

Here is the code outline:

'define string to identify workbook
Dim w2 As String
w2 = "myworksheet.xlsx"

'define ws2 as worksheet to work on
Dim ws2 As Worksheet: Set ws2 = Workbooks(w2).Worksheets(1)

'define working range
Dim ws2r As range
Set ws2r = ws2.range("E2", ws2.range("E2").End(xlDown))

'add conditional formatting to the working range
With ws2

  'see below for .colDiff function
  Set ConditionalRange = ws2r.Offset(0, colDiff("E", "AP")) 

  'The following 4 With...End With statements assign other
  'conditional formats, none of which have problems.
  'I've simplified these statements to outline what's being done.
  'See the last (5th) With...End With statement for
  'the unexpected behavior.

  WithConditionalRange.FormatConditions _
    .add(xlExpression, , ADifferentFormula1)
    .Font.Color = someRGBValue
  End With

  WithConditionalRange.FormatConditions _
    .add(xlExpression, , ADifferentFormula2)
    .Font.Color = someRGBValue
  End With

  WithConditionalRange.FormatConditions _
    .add(xlExpression, , ADifferentFormula3)
    .Font.Color = someRGBValue
  End With

  WithConditionalRange.FormatConditions _
    .add(xlExpression, , ADifferentFormula4)
    .Font.Color = someRGBValue
  End With

  'This With...End With block has unexpected behavior.
  ConditionalRangeFormula = "=ISBLANK($AP1)"
  With ConditionalRange.FormatConditions _
    .add(xlExpression, , ConditionalRangeFormula)
    .Borders.color = RGB(192, 0, 0)
  End With

End With 'with ws2

Here's the "colDiff" function called in the procedure above:

Public Function colDiff(col1 As String, col2 As String) As Long
  With ActiveSheet
    'return the number of columns between col1 and col2
    colDiff = Abs(.range(col1 & "1").Column - .range(col2 & "1").Column)
  End With
End Function

Blackhawk Blackhawk · Accepted Answer · 2014-04-28T16:43:46

I tested this functionality by placing a header "Data" in AP1, placing random data from AP2 to AP16, then deleting AP1,5,7,13 to make BLANKS and the following worked correctly:

Public Sub Test()
    With Range("E2:AP16").FormatConditions.Add(xlExpression, , "=ISBLANK($AP2)")
        .Borders.Color = RGB(192, 0, 0)
    End With
End Sub

Does the above single function work correctly for you? If not, I would suspect that perhaps there are merged cells or some other spreadsheet specific issue going on.

Excel VBA conditional formatting auto-change

1 Answers