Conditional formatting involving blank cells

[hvaleagues]

I have been stumped on this one for a while and have searched the forums for answers, but although finding similar situations I can't seem to get the results I am looking for. Any help would be appreciated.

I have a column which I use to check my entries into other columns and should always produce a 0.00 (unless I made a mistake somewhere). In order to call attention to any mistakes, I would like to highlight these non 0 cells in red. Using conditional formatting, I am able to do this, however it also highlights my blank cells red. I would like to keep the blanks with the gray highlight that the whole column has.

The blank cells in this column actually contain formulas, but show a blank thru the IF command. My formula in these cells is

=IF(DZ2="","",ROUND((O2+Z2)-DZ2,6))

The range I am trying to work on is : EA2:EA809

I have read that a cell that contains a formula is not truly blank and thats why my CF won't work.

Any ideas on how to use CF to get the results I am looking for?

 

[Aladin Akyurek]

Try to invoke the following formula in CF...

=ISNUMBER(1/$EA2)

 

[hvaleagues]

Aladin -

Thanks for the prompt response, however I tried that and it didn't work. I still have shading in my blank cells.

 

[LxQ]

What if conditional formatting instead of using "Cell value", you pick "formula", then enter the following:
=NOT(OR(EA2=0,Round((O1+Z1)-DZ1,6)=0))

 

[LxQ]

actually, this may work better:
=NOT(OR(DZ2=0,EA2=0))

 

[Akinrotimi]

Hi,

The code below should solve your problem:

Option Explicit
CF Blank cells ()
' akinrotimi, 01/09/2011
http://www.mrexcel.com/forum/showthread.php?t=576101
Application.ScreenUpdating = False
Columns("Ae:Ae").Select
Selection.Copy
Columns("dd:dd").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("dd1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
Range("dd1").Select
ActiveCell.Formula = "h"
[dd:dd].SpecialCells(xlCellTypeBlanks).EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
.PatternTintAndShade = 0
End With
[dd1:dd1000].Select
Selection.ClearContents
Range("ae1").Select
Application.ScreenUpdating = true
end sub

Cheers!

Rotimi

 

[Aladin Akyurek]

Aladin -

Thanks for the prompt response, however I tried that and it didn't work. I still have shading in my blank cells.

Select EA2:EA809.
Activate Home | Conditional Formatting | New Rule.
Choose the option "Use a formula...".
Enter the suggested formula:

=ISNUMBER(1/$EA2)

Activate the Format button.
Install the desired formatting.

Just to make sure: The set up picks out the non-zero numeric cells.

This is what get (on a smaller range)...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #fcd5b4; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=19 align=right>12</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #fcd5b4; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=19 align=right>0.4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #fcd5b4; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; mso-pattern: black none" class=xl63 height=19 align=right>-4.4</TD></TR></TBODY></TABLE>

 

[T. Valko]

Select EA2:EA809.
Activate Home | Conditional Formatting | New Rule.
Choose the option "Use a formula...".
Enter the suggested formula:

=ISNUMBER(1/$EA2)

Activate the Format button.
Install the desired formatting.

Just to make sure: The set up picks out the non-zero numeric cells.

This is what get (on a smaller range)...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" width=64 height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" align=right height=19>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; mso-pattern: black none" align=right height=19>12</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" align=right height=19>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; mso-pattern: black none" align=right height=19>0.4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; mso-pattern: black none" align=right height=19>-4.4</TD></TR></TBODY></TABLE>

No need for the ISNUMBER function.

This will do the same thing:

=1/EA2

 

[hvaleagues]

Aladin -

Thanks for the simplified instructions. This time it worked, but only if I typed the formula instead of cutting a pasting it. Wierd.

Anyway, it works great and I thank you for your time!

 

[Aladin Akyurek]

Aladin -

Thanks for the simplified instructions. This time it worked, but only if I typed the formula instead of cutting a pasting it. Wierd.

Anyway, it works great and I thank you for your time!

Great to hear that. Thanks for providing feedback. In CF, the error values do not have adverse effects: Given that, 1/$EA2 would suffice too.