Forum Discussion
Color Coding a Cell based on the value of another Cell
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.
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.
- You are right. That was the mistake in my formula, which I didn't notice until you pointed it out.
Thank you once again.
Truly appreciate your help.