Conditional formatting in MS Excel

  Gary Wood 22:01 27 Jul 06
Locked

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,

Gary

  silverous 22:03 27 Jul 06

How about isblank(<putthecellhere>)

  silverous 22:05 27 Jul 06

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

=ISBLANK($G$2)

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:

=ISBLANK($G2)

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

  Gary Wood 22:11 27 Jul 06

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 06

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

I think we need a fuller explanation.

  silverous 22:19 27 Jul 06

Like this ?

=AND(ISBLANK($H2),NOT(ISBLANK($A2)))

  silverous 22:20 27 Jul 06

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 06

Any good Gary?

  Gary Wood 16:15 30 Jul 06

silverous,

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.

Gary

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

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac