Conditional Formatting in Excel

  [DELETED] 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.

  [DELETED] 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?

  [DELETED] 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!

  [DELETED] 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!

  [DELETED] 16:00 26 May 2006

beynac, got it! Thanks that is really useful.

Never used Conditional Formatting before.

  [DELETED] 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

Microsoft Surface Book 2 15in review

Illustrator Amy Grimes on how setting up her own eco-brand led to success with clients too

MacBook Pro keyboard issues and other problems

Test : l’enceinte connectée HomePod d’Apple