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