Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row Formatting according to cell date entries.
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...." |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row Formatting according to cell date entries.
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row Formatting according to cell date entries.
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Row Formatting according to cell date entries.
Can you post a copy of the spreadsheet so that I can see what's going on? -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if data in one cell, then date in adjacent cell | Excel Worksheet Functions | |||
Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
run a macro in a locked cell | Excel Discussion (Misc queries) | |||
Transfer Cell Formatting for linked cells | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |