Excel 2007 - conditional formatting ignore blanks within a range

S

Steve Harrison

I have a spreadsheet with a column showing the difference between two
other columns. I have applied the following conditional formatting
based on the tolerance level

Cell Value Colour
<1 Green
1 Amber
5 Red

These are absolute numbers as all could be positive or negative.

The formatting applies to cells G3-G63 but in some cases there will be
less than 60 values therefore some of the cells will be blank. I have
tried using ISBLANK to account for this but my macro just keeps
setting any balnk cells in this ranmge as green.

Can someone please take a look at my macro below and let me know how I
can get it to check if each cell is blank or not before applying the
rules.

Macro

Sub ReturnDifference()
'
' ReturnDifference Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Range("G3:G63").Select

With Selection.FormatConditions
.Delete
.Add _
Type:=xlExpression, _
Formula1:="=ABS(G3)>5"
.Item(1).Interior.ColorIndex = 3 'Red
.Add _
Type:=xlExpression, _
Formula1:="=ABS(G3)>1"
.Item(2).Interior.ColorIndex = 44 'Amber
.Add _
Type:=xlExpression, _
Formula1:="=ABS(G3)<1"
.Item(3).Interior.ColorIndex = 4 'Green


End With



End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top