Row Formatting according to cell date entries.

[Sean Smith]

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.
--

"It is better to remain silent and look like a fool than to speak up and
remove all doubt...."

[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

[Sean Smith]

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
--

"It is better to remain silent and look like a fool than to speak up and
remove all doubt...."


"mrice" wrote:

>
> 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
>
>
> --
> mrice
>
> Research Scientist with many years of spreadsheet development experience
> ------------------------------------------------------------------------
> mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
> View this thread: http://www.excelforum.com/showthread...hreadid=537555
>
>

[mrice]

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