Conditional Formatting in Excel

  Gary Wood 09:38 26 May 2006

I want to set up the following conditional formatting in Excel:

For each row, where column D contains "Yes" and Column E is blank, shade the cell in column E yellow. Where column D contains "Yes" and column E is not blank, do not shade column E.

Can anyone advise me of an appropriate forumla?



  beynac 11:22 26 May 2006

Enter the following in the Conditional Formatting dialog box:

Formula Is =AND(D1="Yes", E1="")

then select your required Format (Format | Patterns pick a colour).

  beynac 11:37 26 May 2006

Select cell E1 when entering the formula. If you select a number of cells in column E, the formula will apply to all of them. Alternatively, you can just copy and paste cell E1 into the other cells.

  pj123 14:35 26 May 2006

I would have thought that needed an If, Then, Else statement? But I can't find the Else bit to colour the cell. Maybe a Macro would be better?

  ghm101 15:03 26 May 2006

=IF(D2="Yes",IF(E2 = "",TRUE,FALSE),FALSE)

  beynac 15:28 26 May 2006

No "if" is needed. This is the Conditional Formatting dialog box (Format | Conditional Formatting...) for the cell, not the cell itself. Try it - it works!

  ghm101 15:39 26 May 2006


=AND(D1="Yes", E1="")


=IF(D1="Yes",IF(E1 = "",TRUE,FALSE),FALSE)


beynac =AND is probably a tad more effcient as less seems to be going on

  beynac 15:39 26 May 2006

To clarify: click here

  beynac 15:48 26 May 2006

I didn't realise that you could do it with "if". I've always done it the other way. Useful to know - thanks!

  pj123 16:00 26 May 2006

beynac, got it! Thanks that is really useful.

Never used Conditional Formatting before.

  Gary Wood 00:13 28 May 2006

Cheers, beynac - this worked fine!

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

Elsewhere on IDG sites

Samsung Galaxy A8 review: Hands-on

Illustrator Juan Esteban Rodriguez on creating highly detailed official film posters for Star Wars…

iMac Pro review

Meilleurs drones (2018)