Conditional formatting x amount of cells based off of 1 other cell

Occasional Visitor

Hello all, 

I am trying to get multiple cells highlighted based off of 1 number. For example, if i have the number 10 in A1, i would like A2-A12 to be highlighted a certain color. I would also like to give different colors to different amounts(1-4 red; 4.01-8 yellow; 8.01+ green). Is this a possibility?

1 Reply


Select A2:A12.


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




Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.


Repeat these steps, but with the formula =A$1>4 and yellow.

Repeat them again with the formula =A$1>8 and green.