Conditional Formatting in Excel

  Gary Wood 09:38 26 May 06
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 06

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 06

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 06

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 06

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

  beynac 15:28 26 May 06

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 06

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 06

To clarify: click here

  beynac 15:48 26 May 06

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 06

beynac, got it! Thanks that is really useful.

Never used Conditional Formatting before.

  Gary Wood 00:13 28 May 06

Cheers, beynac - this worked fine!

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

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…