How to use conditional formatting for values that are a certain amount higher than the above cell?

Question

I'm having trouble using Conditional Formatting in Excel. I am inexperienced so apologies if this is an easy fix.

Basically, I have a table of accounts, and the number of orders they have in a matrix style. For each order they make, the number of days since they made that order is added into the table.

Order Quantity is the order number, so the 9th order Clara made was 2416 days ago

I wanted to show that if, for example, Abbie's 3rd order was over a year after her 2nd order (which it is), then highlight it red. If it's between 6 months and a year, yellow, and if it's under 6 months to make it green (if it's possible to have more choice that would be even better, so last 30 days, 90 days, etc.).

However, when I write this into the formula section of the conditional formatting area, and drag the formatting down, the cell numbers stay the same, so they would all base their values on whether the 5th, 6th, 7th orders were over a year since her 2nd order.

I wanted to make it to show the 3rd against 2nd, 4th against 3rd, 5th against 4th and so on. (And obviously I would like to do this with all accounts). How would I go about this?

---

The only way I can even think of it working after the problems I've had is to do them all individually, which would be very time consuming.

=$J$5 < $J$4 - 365 is the formula that is written. I've gotten rid of the $ but they automatically reappear, and even when they don't, the cells don't change.

Answer 1

This time I'll translate for the non-fake-German speakers..

The formula should not be with absolute references when the conditions aren't absolute, i.e. $B$2<$B$1-365 over the range =$B$2:$G$13 would color all cells in that range red since B2 is in fact smaller than B1 - 365

The formulae in English:

• AND(ISNUMBER(B1),B2>B1-180)
• AND(B2<B1-180,B2<B1-365) (yes, I had 360 in the img, don't ask why)
• B2<B1-365 (no change there ofc)