Conditional Formatting Macro 1

[sandybam]

Hi All,

I have an Excel spreadsheet with the following conditional formatting set in a macro. When this macro runs it highlights fields that are null. How can I only highlight those fields that are populated? Here is what we have:

Columns("H:H").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="3.5"
Selection.FormatConditions(1).Interior.ColorIndex = 36

Thank you for your help!

Sandy

 

[dhulbert]

Sandy

The macro appears to hi-lite cells where the cell is <=3.5 not where it's null



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[AnotherHiggins]

sandybam,

Replace

[red]Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="3.5" [/red]

with

[blue]Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(D1<=3.5,ISBLANK(D1)=FALSE)"[/blue]

dhulbert,
In the posted code, null values are considered less than 3.5.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Grr. Oh how I would for an edit feature.

In your code, you'll want to use H instead of D.

Like this:
[blue]Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(H1<=3.5,ISBLANK(H1)=FALSE)"[/blue]

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.