Conditional formatting involving blank cells

H

hvaleagues

New Member
Joined
Sep 25, 2009
Messages
9
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
Code: 
=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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Aladin -

Thanks for the prompt response, however I tried that and it didn't work. I still have shading in my blank cells.
 
Upvote 0
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))
 
Upvote 0
Hi,

The code below should solve your problem:
Rich (BB code): 
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
 
Last edited:
Upvote 0
hvaleagues said:
Aladin -

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

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>
 
Upvote 0
Aladin Akyurek said:
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>
Click to expand...
No need for the ISNUMBER function.

This will do the same thing:

=1/EA2
 
Upvote 0
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!
 
Upvote 0
hvaleagues said:
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!
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
conditional formatting for rows with blank cells
Replies
9
Views
430
Joe4
Joe4
M
  • Solved
Conditional Formatting - Highlight blank cells
2
Replies
18
Views
1K
Fluff
Fluff
S
  • Solved
Conditional format to highlight future dates
Replies
3
Views
320
Joe4
Joe4
Pinaceous
  • Solved
Conditional Format
2 3
Replies
21
Views
573
Pinaceous
Pinaceous
P
  • Solved
Conditional Format Selected Cell
2
Replies
12
Views
214
Pacman52
P

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

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