Conditional Formatting in excel based on another column
You can do it as follows (I'll assume that the 4.9755
is in cell A1
, change as applicable):
Select the range of
A1
toA10
.Pull up the conditional formatting dialog and select the option that requires a formula.
Put the formula
=B1>0
and pick the formatting with font as green.Press OK and repeat for the negative values, except using
=B1<0
for the formula and the font as red for the formatting.
The number formatting doesn't need conditional formatting, you can simply select the two columns and format as appropriate.
If you want to format both columns at the same time, you can select both colums and use the formula =$B1>0
for green and =$B1<0
for red:
Jerry's answer is correct. Excel is adaptive, but you must make sure the formula looks exactly like he shows. If you click on cell B1 the formula will show =$B$1>0 rather than =$B1>0. The formula cannot contain the $ before the number 1 or it will not be adaptive. The $ symbol locks the formula, which is helpfull in other situations, but not here.