Row Formatting according to cell date entries.

[Guest]

I'm a sales rep and as part of my job I have to see each of my customer every
90 days maximum. If I fail to do so then those customers not seen become
open to poaching by my colleagues.

I'm setting up a spreadsheet which contains all my customer data and am
applying conditional formatting to a column containing the last date I saw
each customer.

This results in the date being black normal font if I saw them under 30 days
ago, orange font for between 30 and 75 days so that I know I need to book
appointments with them, red bold font for 75 to 90 days to warn me I'm about
to lose them, and bold black strikethrough to let me know that if I haven't
seen them after 90 days I have very likely lost them to a colleague.

This is all done in the conditional formatting menu using the "Cell is"
sellection with less than or equal to.

Although it works well what I really want is for the entire row (Customer
Name, date last seen, address, telephone no, etc) to be highlighted according
to the above criteria.

Any help would be greatly appreciated.

Thank you.
Sean.

 

[mrice]

Sean

You could do this with a macro linked to the worksheet_open event

Something like

Sub Update()
Const RedHighlight = 90
Const YellowHighLight = 80
Const DateColumn = 3


For N = 2 To Cells(65536, 1).End(xlUp).Row
If Cells(N, DateColumn) + RedHighlight < Now Then
Rows(N).Interior.ColorIndex = 3
GoTo FinishedThisRow
End If

If Cells(N, DateColumn) + YellowHighLight < Now Then
Rows(N).Interior.ColorIndex = 6
GoTo FinishedThisRow
End If

FinishedThisRow:
Next N
End Sub

 

[Guest]

Hi Mike, thank you for the reply. I tried that macro but it throws up an
error 13 at the one line of code.

Thanks
Sean

 

[mrice]

Can you post a copy of the spreadsheet so that I can see what's going
on?