Conditional formatting in MS Excel

  Gary Wood 22:01 27 Jul 2006

I want to shade rows on a spreadsheet which have no value in a specific cell.

I know I need to use conditional formatting, but I can't figure out an appropriate formula to use.

Can anyone advise?

Many thanks,


  silverous 22:03 27 Jul 2006

How about isblank(<putthecellhere>)

  silverous 22:05 27 Jul 2006

so, if it is cell g2 you want to format based on you make your condition a "formula is" type condition and enter:


as the formula.

If you want it to be able to be copied down but always based on column G (say) then you freeze column G as follows:


which allows 2 to become 3,4,5,6 etc. as it is pasted down.

  Gary Wood 22:11 27 Jul 2006

Many thanks, silverous. That works perfectly.

However, I've realised a problem. Because the formula is shading rows where the cell in column H is blank, it's shading all the rows after the last row containing data.

So, I need to modify the formula to say where column A is not blank and where column H is blank, shade the row.

How can I do this?

  VoG II 22:18 27 Jul 2006

Where did A and H come from - not mentioned above.

I think we need a fuller explanation.

  silverous 22:19 27 Jul 2006

Like this ?


  silverous 22:20 27 Jul 2006

I think he's saying he wants to copy it all the way down the sheet but if there's no data at the left, (i.e. in A) he doesn't want the conditional formatting based on H because if the entire row is empty there's no data at all and it makes no sense to conditional format it. That was my take on it anyway!

  silverous 12:40 28 Jul 2006

Any good Gary?

  Gary Wood 16:15 30 Jul 2006


Yes, your interpretation was correct and your suggestion worked perfectly.

Many thanks for your help and please accept my apologies for the delayed reply - I've been away from my computer the last couple of days.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

HP’s new Surface Pro rival is designed specifically for Adobe-using designers and artists

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?