Conditional formating Solved/Closed

Question

I have a spreadsheet with Excel 2013 that has text drop down lists. I want to use conditional formatting to highlight with a different cell color, depending on the choices from the same drop down list. I want it to be one color if it is the same choice, a different color if it is higher on the list and another different color if it is lower on the list.
e.g. the drop down is: apple, plum, banana, lemon, mango
Cell A1 is plum
If Cell B1 is plum, I want the cell to be green
If Cell B1 is banana, lemon or mango, I want the cell to be blue
If Cell B1 is apple, I want the cell color to be red

I'm ok if the choice from the list is the same but is there a way to set up the formatting to obtain the results that I would like?
P.S. I am definitely not an Excel ninja.

zs1986 · Answer

Hi jgmb,

You can add Home --> Conditional Formating in following manner.

1. You can have Drop down list in A2:A6 range of excel
2. Define the name for list by selecting it say Fruits
3. Then as per you requirement you can Select Cell B2 --> Data Validation --> List --> In Source field write  =Fruits
4. Similarly for cell C2 or else copy cell B2 and paste at C2
5. Then you will find Drop downs at B2 and C2 with the desired values
6. Then Select C2 cell and Click on Conditional Formatting --> New Rule --> Use a formula to determine Which cells to be format option
7. Then Enter formula as =IF(MATCH($C$2,$A$2:$A$6,0)=MATCH($B$2,$A$2:$A$6,0),TRUE,FALSE)
 and click on Format button --> Fill --> Select Green color
8. Then Similarly create a new rule on the same Cell C2 with formula as =IF(MATCH($C$2,$A$2:$A$6,0)<MATCH($B$2,$A$2:$A$6,0),TRUE,FALSE)
 and click on Format button --> Fill --> Select Blue color
9. Then Similarly create a new rule on the same Cell C2 with formula as =IF(MATCH($C$2,$A$2:$A$6,0)>MATCH($B$2,$A$2:$A$6,0),TRUE,FALSE)
 and click on Format button --> Fill --> Select Red color

zs1986 · Answer

Make Red Color in front of Point No 8 and Blue color in front of Point no. 9

jgmb · Answer

Thanks very much for your help! These formulas work perfectly in the two primary cells. However, I'm running into difficulty copying the conditional formatting to another cell. I've tried ordinary copy and paste, format painter and paste special (formula) What am I doing wrong?

jgmb · Answer

Hrmph! Now that I've tried to copy the formatting, the original cell's conditional formatting doesn't work and I didn't change anything. Only the first of the three formulas still work. The other don't when I change the content of the two cells. What am I doing wrong?

jgmb · Answer

I got!! I needed to remove the $ in front of the cell number of the cell that was to be formatted. Not sure what happened to the original conditional formatting but when I re-entered it, everything worked. I was able to copy the formatting successfully by double clicking on format painter.

Thanks for your help!

zs1986 · Answer

Welcome.. Yes..you should remove $ from the cell number that you want to be formatted..

zs1986 · Answer

Welcome.. Yes..you should remove $ from the cell number that you want to be formatted..

zs1986 · Answer

Welcome.. Yes..you should remove $ from the cell number that you want to be formatted..

zs1986 · Answer

Welcome..Yes.. you should remove $ from the cell that you to format.

jgmb · Answer

Thanks again!

Conditional formating

10 responses

Similar discussions

Subscribe To Our Newsletter!