Conditional Formatting based on a different cell

[yangstar]

Hi.

I was wondering if there's a formula I could use to format a cell's background color based on the value in a different cell? I have values in cells A2 - A200. I want the cell below the cell directly above it to change a certain color if it's value is greater than or less than the value directly above it (sorry, confusing). For example, cell A2 will have the starting value, say 1. I want cell A3 to change to yellow if it's value is lower than the value in A2 or green if it's value is greater than A2. Then, I want cell A4 to do the same depending on it's value and A3's value. So on and so forth all the way down to A200 and beyond if I continue to add more values. I guess you can look at this as trending.

Thanks,
yangstar

 

[MDCurry]

I was able to get this to work (in Excel 2010). You will need 2 formulas.

Given your example:
1. select cell A3
2. Alt, H, L, N and select "Use a formula to select which cells to format"
3. Enter the 1st formula:
=IF(A3<A2,TRUE,FALSE) and select the format/fill you want and Apply the formats
4. while still at cell A3: Alt, H, L, R (to "Manage rules")
5. Select "New Rule"
6. select "Use a formula to select which cells to format"
7. Enter the 2nd formula:
=IF(A3>A2,TRUE,FALSE) and select the format/fill you want and Apply the formats
8. Copy the formats to the range you wish to apply

 

[Robert Mika]

Hi.

I was wondering if there's a formula I could use to format a cell's background color based on the value in a different cell? I have values in cells A2 - A200. I want the cell below the cell directly above it to change a certain color if it's value is greater than or less than the value directly above it (sorry, confusing). For example, cell A2 will have the starting value, say 1. I want cell A3 to change to yellow if it's value is lower than the value in A2 or green if it's value is greater than A2. Then, I want cell A4 to do the same depending on it's value and A3's value. So on and so forth all the way down to A200 and beyond if I continue to add more values. I guess you can look at this as trending.

Thanks,
yangstar

You did not specify what if the cell is equal to the one above.
So I left them white,

Xxample:
https://dl.dropboxusercontent.com/u/78583609/cff.xlsx

 

[LxQ]

click on A2
go to Conditional formatting
use the following formula for the conditional formatting
=A2>A1
and set the formatting to green..

you can add another rule
=A2<A1
and set it to red

now when you're back in cell A2, copy it down to 200.. either by copy and paste
or copy and paste special -> paste formatting
or drag the little plus sign in the corner of A2 all the way down to 200 and then click on the arrow and select "Fill formatting only"

 

[yangstar]

Thanks Everyone for the responses and examples. These methods should work for me.

 

[Robert Mika]

Thanks Everyone for the responses and examples. These methods should work for me.

You are welcome