Difference Between Menu-Driven CF and VBA-Driven CF

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,206
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
Rich (BB 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.
Rich (BB code):
.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?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Code:
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

Board Regular
Joined
Oct 22, 2010
Messages
120
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,206
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Code:
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:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top