Conditional formating

Solved/Closed
jgmb Posts 5 Registration date Tuesday July 2, 2013 Status Member Last seen July 9, 2013 - Jul 2, 2013 at 06:37 PM
jgmb Posts 5 Registration date Tuesday July 2, 2013 Status Member Last seen July 9, 2013 - Jul 9, 2013 at 09:26 AM
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.

10 replies

zs1986 Posts 7 Registration date Wednesday July 3, 2013 Status Member Last seen July 22, 2013 1
Jul 3, 2013 at 10:20 AM
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
1
zs1986 Posts 7 Registration date Wednesday July 3, 2013 Status Member Last seen July 22, 2013 1
Jul 3, 2013 at 10:25 AM
Make Red Color in front of Point No 8 and Blue color in front of Point no. 9
0
jgmb Posts 5 Registration date Tuesday July 2, 2013 Status Member Last seen July 9, 2013
Jul 8, 2013 at 12:12 PM
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?
0
jgmb Posts 5 Registration date Tuesday July 2, 2013 Status Member Last seen July 9, 2013
Jul 8, 2013 at 04:01 PM
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?
0

Didn't find the answer you are looking for?

Ask a question
jgmb Posts 5 Registration date Tuesday July 2, 2013 Status Member Last seen July 9, 2013
Jul 8, 2013 at 05:52 PM
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!
0
zs1986 Posts 7 Registration date Wednesday July 3, 2013 Status Member Last seen July 22, 2013 1
Jul 9, 2013 at 06:57 AM
Welcome.. Yes..you should remove $ from the cell number that you want to be formatted..
0
zs1986 Posts 7 Registration date Wednesday July 3, 2013 Status Member Last seen July 22, 2013 1
Jul 9, 2013 at 06:59 AM
Welcome.. Yes..you should remove $ from the cell number that you want to be formatted..
0
zs1986 Posts 7 Registration date Wednesday July 3, 2013 Status Member Last seen July 22, 2013 1
Jul 9, 2013 at 06:59 AM
Welcome.. Yes..you should remove $ from the cell number that you want to be formatted..
0
zs1986 Posts 7 Registration date Wednesday July 3, 2013 Status Member Last seen July 22, 2013 1
Jul 9, 2013 at 07:01 AM
Welcome..Yes.. you should remove $ from the cell that you to format.
0
jgmb Posts 5 Registration date Tuesday July 2, 2013 Status Member Last seen July 9, 2013
Jul 9, 2013 at 09:26 AM
Thanks again!
0