Color Coding a Cell based on the value of another Cell

[AshTheDash]

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

[#2]

@AshTheDash 

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.

[#3 · Accepted solution]

Hi @Hans Vogelaar 

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]

@AshTheDash 

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.

[#5]

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.