Difference Between Menu-Driven CF and VBA-Driven CF

[JoeMo]

If I conditionally format say Range A1:A10 in Excel 2007 using the CF menu and select 'NewRule/Format all cells based on their values' and set the minimum type to Number and the value to 0 and the Maximum type to number and the value to say 40: then set the format to fill the cells that meet these conditions with a yellow fill color, I get what I expected. In particular, any cell with a numeric value from 0 through 40 has a yellow fill. Blank (empty) cells in the range do not have a fill, as expected.

Now suppose I use the following code:

Sub TestCF()
Dim rng As Range
Set rng = Range("A1:A10")
With rng
    For i = .FormatConditions.Count To 1 Step -1
        .FormatConditions(i).Delete
    Next i
    .FormatConditions.Add xlCellValue, xlBetween, 0, 40
    .FormatConditions(1).Interior.ColorIndex = 6
End With
    
End Sub

I find that any empty cells in the range get a yellow fill, not what I expected, otherwise the result is identical to the menu-driven CF.

If I make the following one-line change to the code, I get the same result as the menu-driven CF, that is, blank cells are not filled.

.FormatConditions.Add xlCellValue, xlBetween, 1E-32, 40

Where I have changed 0 to an arbitrarily small number greater than 0.

Any thoughts on why there is this apparent difference, and how to prevent blank cells from being filled with color if the 0 is replaced by a negative number?

 

[Scott Huish]

I tried your example and blank cells got filled either way.,

However, there is no need for your For Next loop.

It can be done without the Select, but I find it easier:

Sub test()
Range("A1:A10").Select
With Range("A1:A10").FormatConditions
    .Delete
    .Add Type:=xlExpression, Formula1:="=AND(ISNUMBER($A1),$A1>=0,$A1<=40)"
    .Item(1).Interior.Color = vbYellow
End With
End Sub

 

[alan.m]

I tried both ways and got the same as you (Excel 2010).
I think the situation arises because in the VBA you are doing maths on the blank cell, so Excel converts it to a numeric value, and zero makes sense.
<TABLE id=topTable width="100%"><TBODY><TR id=headerTableRow1><TD align=left>Excel Developer Reference</TD></TR><TR id=headerTableRow2><TD align=left>FormatConditions.Add Method</TD></TR></TBODY></TABLE>Adds a new conditional format.
Syntax
expression.Add(Type, Operator, Formula1, Formula2)
when you use the value 1E-32 it is greater than zero, so you get your expected results.
HTH

 

[JoeMo]

I tried your example and blank cells got filled either way.,

However, there is no need for your For Next loop.

It can be done without the Select, but I find it easier:

Sub test()
Range("A1:A10").Select
With Range("A1:A10").FormatConditions
    .Delete
    .Add Type:=xlExpression, Formula1:="=AND(ISNUMBER($A1),$A1>=0,$A1<=40)"
    .Item(1).Interior.Color = vbYellow
End With
End Sub

HOTPEPPER,
Thanks for the tip on not needing the For Next loop to remove existing CFs.
I was commenting on the use of VBA when the type argument is xlCellValue. Your solution using xlExpression is a good one. I came up with the following solution using xlCellValue:

Sub TestCF()
Dim rng As Range, cf As FormatCondition
Set rng = Range("A1:A10")
With rng.SpecialCells(xlCellTypeConstants, xlNumbers)
    .FormatConditions.Delete
    .FormatConditions.Add xlCellValue, xlBetween, 0, 40
    .FormatConditions(1).Interior.ColorIndex = 6
End With
    
End Sub

I didn't bother to add an error trap in case there are no cells that meet the special cells criteria, but that's an easy addition.

Alan,
Thanks for confirming that Excel 2010 reproduces the behavior I noted with 2007.