Forum Discussion
Color Coding a Cell based on the value of another Cell
Hi,
I want to highlight/color code the Cell in Column B, based on the value of cell in Column A
If value of Cell A1<0, B1 shd be filled with Red Colour
Also want to add one more condiiton or create a separate opp Ccondition
If value of Cell A1>0, B1 shd be filled with Green Colour
| A | B |
| -4.55 | 19,190.00 |
| -2.55 | 19,200.00 |
| -1.55 | 19,210.00 |
| -0.55 | 19,220.00 |
| 0.45 | 19,230.00 |
| 1.45 | 19,240.00 |
| 2.45 | 19,250.00 |
| 3.45 | 19,260.00 |
Hi HansVogelaar
Thank you for your quick reply.
I tried the same but it did not work earlier.
Now its working.
However, I have a unique issue at hand. There is an offset in the A and B Columns.
See this image:
Also, if I change value in A28, B34 is changing its value.
A32 onwards its positive and that can be seen from B38 onwards.
See this image. Not sure what is going wring here.
4 Replies
Select the cells in column B that you want to color.
B1 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$A1<0
Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.Repeat these steps, but with the formula
=$A1>0
and green as fill color.
Hi HansVogelaar
Thank you for your quick reply.
I tried the same but it did not work earlier.
Now its working.
However, I have a unique issue at hand. There is an offset in the A and B Columns.
See this image:
Also, if I change value in A28, B34 is changing its value.
A32 onwards its positive and that can be seen from B38 onwards.
See this image. Not sure what is going wring here.
When you select the range in column B to color, the active cell in the selected range should be the first (topmost) cell in that range.
For example, if you select B6:B100, B6 should be the active cell in the selection.
And the formulas in the conditional formatting rules should refer to the cell in that row.
So in this example, the formulas should be =$A6<0 and $A6>0 since the active cell is in row 6.
