Previous Topic Next Topic

How To Apply Conditional Formatting in Excel

Solved/Closed
Mike - Nov 24, 2010 at 12:42 PM
 amazed - Dec 30, 2015 at 08:17 PM
Hello,

I need to apply conditional formatting to range H1:H10000 based on corresponding values in another column, G1:G10000.

If the value in the G cell = 1, then I want the H cell to be blue and bold.

If the value in the G cell = 2, then I want the H cell to be purple and bold.

If the value in the G cell = 3, then I want the H cell to be red and bold.

Otherwise, I want no formatting.

For example, if cell G65 = 3, then I want cell H65 to be red and bold.

Any help is much appreciated.

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 25, 2010 at 09:37 AM
Hi Mike,

Select cell H1, goto top menu's > format > conditional format.

Condition 1:
formula is: G1=1 apply desired format.
Condition 2:
formula is: G1=2 apply desired format.
Condition 3:
formula is: G1=3 apply desired format.
Click OK.

Drag cell H1 down.
If you already have data in column H, select "format only" from the small square that appears when you are donw dragging.

Best regards,
Trowa
65
JustBrowsing
Dec 10, 2012 at 03:59 PM
The questions wants formatting based on Cell G of the corresponding row. Not always G1.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 10, 2012 at 06:38 PM
@ JustBrowsing , when you copy formula in excel it copies relatively in most case. So when you paste same formula in H2, reference to G1 should change to G2
Other way of doing same would be
1. Select Cell H1
2.Then starting from cell H1 to H10000 , select all cells
3. set the conditions as Trowa suggested (https://ccm.net/forum/affich-503533-how-to-apply-conditional-formatting-in-excel#1)
In such case you would not have to copy any anthing, but idea remain same, excel would use relative locations unless you make absolute reference by using $ sign in cell address
0
Rona
Mar 18, 2013 at 10:59 PM
what will I do to change the font color of the data cell using dropdown list. I mean for example in A1 where the dropdown list is created I chose red . how will the data cell in B1 turns red also and if I chose again green the data will change also.
how is that.
any help?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 19, 2013 at 08:50 PM
From what I am getting, what color should be used is in dropdown in A1. Based on the color choosen, you want to color the cell B1. In such case you are better off not using condtional format but macro. But even if you want to use conditional format, what TrowaD said is still true
0
Exclerator
May 15, 2013 at 06:31 PM
I used =if(g1=1,true,false) for the first rule, =if(g1=2,true,false) for the second and =if(g1=3,true,false) for the third. Worked for me.
0

Similar discussions

How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Highlight cell if another cell contains any text
LvH -
LvH -

4 responses