Conditional format on adjacent cells

S

Simon

Hi All,
what am I doing wrong??
I want cells A1 to G1 to conditionally format (to red) if they are ALL
empty, but if ANY one (or more) of them contain data (text) then NONE of them
should format.
I tried the formula =ISBLANK(A1:G1) then asigned a format, but for some
reason A1 only responds to the data in A1 and seems to ignore the rest of the
range, I've also tried similar range with formula ="" and cell value is equal
to "".
I am sure this must be possible but just can't find it.
I'v been at this for three weeks now. Please put me out of my missery!!
Please - no 'code' responses, 'CF' only. Thanks.
 
O

OssieMac

Hi Simon,

ISBLANK applies to a specific cell; not a range of cells. Need to count the
cells with data or count the blank cells.

Try the following. Note the absolute addressing for $A$1:$G$1 otherwise the
range will change for each cell in the range.

=COUNTA($A$1:$G$1)=0

You could also use =COUNTBLANK($A$1:$G$1)=7. However, not generic. If you
change the range then need to change the 7.
 
P

pasket_YM

Try this Simon and let me know how it works out for you. Highlight the range
of cells to which you want to apply the condional format. In this case A1:G1.
Click 'Conditional formatting" (I don't know which office version you are
using). Assuming you are using excel 2007, choose new rule in the drop-down
menu. In the new formating rule window, select the second option "Format only
cells that contain". In the Edit Format Description, under "Format onlly
cells with" Make cell value is shown in the firs box, choose "equal to" in
the second box and type ="" in the third box. click format in the preview
andf in the fill tab, select the colou want. If that helps you, please leave
feedback.
 
S

Simon

Nice one OssieMac, your first offering is exactly what I needed.
Thanks very much.
Simon.
 
S

Simon

Thanks pasket YM,
I should have said what version I was using. It's 2003 not 2007 - but thanks
for the tip, I'll be getting 07 soon and it sounds like there are some good
options to find my way around.
Cheers.
Simon.
 

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