How to change a cells color depending on the value of the cell above it/before it for hundreds of lines

Question

I have hundreds of lines with varying values (currency) and want the cell to change color depending on the value above it eg. Greater than = Green, Less than = Red = Equal to = Yellow. ie:

• A1 is $100
• A2 is $200 so should be green (> A1)
• A3 is $150 so should be red (< A2)
• A4 is $150 so should be yellow (= A3)
• So on....

My VERY basic understanding of conditional formatting (Highlight Cells Rules > Greater Than/Less Than/Equal to) is not efficient in this scenario due to the number of cells involved and the fact that numbers will be added daily so I need the solution to be automated for future use.

Answer 1

You can use a formula based approach to the conditional formatting rules to get your desired output.

Steps

Select Cells

1. Click the first cell where you want the conditional formatting to start (A2)
2. Drag the selection to all cells where you want the rules to apply (A2:A1000)

Open Conditional Formatting

3. Click Conditional Formatting from the Home ribbon (top menu)
4. Choose New Rule
5. Choose Use a formula to determine which cells to format

Enter Formulas

You'll need 3 formulas, one for each color

6. For Green (Greater than the cell above):

• Enter the formula: =A2>A1 (assuming you're starting from A2)
• Click on the Format button, go to the Fill tab, and choose a green color.
• Click OK.

7. For Red (Less than the cell above):

• Again, choose New Rule > Use a formula to determine which cells to format.
• Enter the formula: =A2<A1
• Click on the Format button, go to the Fill tab, and choose a red color.
• Click OK.

8. For Yellow (Equal to the cell above):

• Once more, choose New Rule > Use a formula to determine which cells to format.
• Enter the formula: =A2=A1
• Click on the Format button, go to the Fill tab, and choose a yellow color.
• Click OK.

You can click OK to close any remaining dialog boxes. Your settings should be applied. To edit any of these formulas or other settings, highlight the cells with the conditional formatting applied, and select Manage Rules from the Conditional Formatting ribbon.