Conditional Formatting & Blank Cells

[Dominique Feteau]

I have a large sheet with about 1000 rows and 9 columns. I want each blank
cell to be filled with a color i specify (lets say yellow). How can I do
this with Conditional Formatting? I tried using the ISBLANK function, but
it didnt work, or i didnt use it correctly. Any help would be great.

Dominique

 

[Norman Harker]

Hi Dominique!

Select the range (e.g. A1:I1000)
Format > Conditional formatting
Formula is:
=ISBLANK(A1)
Press Format button
Choose format to taste
OK
OK

Note that a formula returning "" is not regarded as blank
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
try the following
- select all cells
- goto conditional format and enter the formula
=A1="" -> replace A1 with the upper left cell of your
selection
- choose your format

 

[Norman Harker]

Hi Dominique!

Note the different results from Frank and my responses.

If you use ISBLANK then a formula that returns "" will *not* be
treated as blank (because it contains a formula) and will not get your
highlight.

If you use "" then both blank cells and formulas returning "" will be
treated as blank and will get your highlight.

Which formula you use depends upon what you want.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dominique Feteau]

Norman...

So my Conditional Formatting should be setup as Cell Value Is -> Equal to ->
"" ?

 

[David McRitchie]

and if you use
=TRIM(A1}=""
you would also include cell that include only 1 or more spaces.