Conditional Formatting in Excel

  Gary Wood 09:38 26 May 2006
Locked

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?

Thanks,

Gary

  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

Both

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

and

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

work

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

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?