Difference Between Menu-Driven CF and VBA-Driven CF

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,901
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?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
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,901
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,945
Members
430,329
Latest member
asmith75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top