Using Conditional Formatting to format cells in drop down list

Akash

Not sure if you're referring to Robert Pellan's question yesterday Small problem with the conditional formatting of a cell (where Icon sets were used) but here's a solution to your needs...

See the attached file where I've added some extra rows to yours and applied the conditional formatting you need (hopefully).

I've created three conditional formatting rules for cell D2 (initially), each with the form as follows:

=IF($D2="<Condition text>",1)

missing the $ before the 2 and where  <Condition text> is replaced by Complete, In Process or Hold (or you could refer to the cells in the list of your data validation rules).

If the contents of D2 match the <Condition text> then the If statement returns the value 1 (logical True) and I apply formatting as follows:

• The Complete rule to give a green fill,
• In Process to  give a yellow fill,
• Hold to give a red fill with white text (contrast is better for reading).

Once they work in D2, just go to Home ribbon/ Conditional Formatting / Manage Rules and Edit each to change the range of the rule to apply from =$D$2 to =$D$2:$D$7 (or whatever row) and the rule will apply in column D. If you insert rows between 2 and 7, the rule will apply but not if you add data beyond 7 (without copying the formatting). You can always correct the range under the Manage Rules

Change the range to =$A$2:$D$7 and the rule will apply across A:D (i.e. the row will be green or whatever.

Full fist of rules (without formatting is:

Formula                 Range
=IF($D2="Hold",1)       $D$2:$D$7
=IF($D2="In Process",1) $D$2:$D$7
=IF($D2="Complete",1)   $D$2:$D$7