Create coloured cells depending on value of cell

[WILLPOU]

Hi

I am trying to make excel colour a certain cell depending on how many months arrears a tenant is in.

Aiming to create a traffic light system i.e those who are in line with their payments cells will be coloured green, those who are in arrears of less than 1 month will be yellow. Those who are 1 month and below 2 month will be amber and those who are more than 2 months in arrears will be red.

I have produced a mock workbook which I hope will explain this more effectively.

Any suggestions will be welcomed

Many thanks

Will

[CRIMEDOG]

Conditional Formatting
Set up 4 rules for the Owed column
New - Use Fomula - Set Formating for fill
Green:=$F3=$F$18
Yellow:=AND($F3<0,$F3>(-1*$B3))
Orange:=AND($F3<((-1*$B3)+1),$F3>((-1*$B3)*2))
etc...

[CRIMEDOG]

oh, I had 0 in F18 but you would do the formula =$F3=0

[WILLPOU]

Hi

Could you elaborate on how to do that. I'm not familiar with conditional formatting

Thanks

Will

[CRIMEDOG]

Highlight the column, go to Conditional formatting, manage, then edit each rule to see what I did.
when you are setting up conditional formatting for a range:
To make the rule refer to a certain column use $ as in $B3 (this points the formula to column B, and relative location row 3)
When I use F3 - this sets the Column as relative AND the row as relative
Note: my range that I applied the formatting to was F3:F6 J3:J6 and N3:N6 -so F3 in the formula applies to F3,J3, & N3

Sorry I missed your reply, I hope this helps

[WILLPOU]

That's helped a lot.

Quick question when I copy the formula you have used and click ok it changes it from this =F3<((-1*$B3)*2)+1 to =XFB3<((-1*$B3)*2)+1 any ideas why it's doing that?

Thanks

[CRIMEDOG]

because it is screwy - when I create a formula in conditional formatting - it will do stuff like that, sometimes it makes the cell reference different.
I have gotten into the habit of setting up the formatting, saving, and immediately going back to edit it.
Once you fix it and save, it will work.

I am sure there is a good explanation, I just don't know it.