Using Conditional Formatting to format cells in drop down list


Dear Members,

Similar question has been raised and answered in the forum but still it is unclear to me.

In sample sheet attached, in the last column D (F&F Status), I want the following formatting options to be imposed in the drop down list:

1. Cell = "Complete" (Cell should be filled as Green)

2. Cell = "In Process" (Cell should be filled as Yellow)

3. Cell = "Hold" (Cell should be filled as Red)

Thanks and Regards,

Akash Sharma



Selected Answer


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


Akash. Are you happy with my Answer/file?
John_Ru (rep: 502) Nov 26, '20 at 5:05 am
Thanks Akash- you're a man of few words in seems!
John_Ru (rep: 502) Dec 1, '20 at 9:37 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login