Highlighting rows by date [Solved/Closed]

Report
Posts
3
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 10, 2010
-
Posts
3
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 10, 2010
-
Hello,
I have a spreadsheet in which the date in a cell has a large amount of impact on what needs to happen with that current individual. What I want to know is if there is a way to,

(1) Have excel highlight the row if the date in H2 is after today's date (as the file is opened)?
(2) Is it possible to highlight the row in a different color if lets say the date in H2 is a month or less away from today's date (as the file is opened)?

Any guidance on this matter would be very helpful even if only the first part can be done it will make a large part of a normal task very much easier on myself, the others in my office and the people that will come after us.


2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
in the first message you talked about only H2 and now it is I3 to I263

I suppose I3 to I263 is correct where the dates are parked.

in this case open vb editor, click control+R. in the project window go to your file and right click "thisworkbook" and park this event code

Private Sub Workbook_Open()
Dim r As Range, c As Range
Worksheets("sheet1").Activate
ActiveSheet.Cells.Interior.ColorIndex = xlNone

Set r = Range("I3:I263")
For Each c In r
If c > CDate(Date) And c <= CDate(Date + 30) Then
c.EntireRow.Cells.Interior.ColorIndex = 6
ElseIf c > Date + 30 Then
c.EntireRow.Cells.Interior.ColorIndex = 3
End If
Next c
End Sub


taking into account basic colors no. 6 is yellow and 3 is red.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2893 users have said thank you to us this month

Posts
3
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 10, 2010

I am sorry about that I wasnt given the full idea of what was wanted untill after the fact and I asked on what I knew. This works outstandingly and I thank you very much.
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
open vb editor (alt+F11). hit control+R. in the project go to your file name and right click "thisworkbook" and click "view code". In the resulting window copy tis event code


Private Sub Workbook_Open()
Range("H2").EntireRow.Cells.Interior.ColorIndex = xlNone
If Range("H2") > CDate(Date) And Range("H2") <= CDate(Date) + 30 Then
Range("H2").EntireRow.Cells.Interior.ColorIndex = 6
ElseIf Range("H2") > Date + 30 Then
Range("H2").EntireRow.Cells.Interior.ColorIndex = 3
End If

End Sub


whenever you open the file this event code will be executed.
Posts
3
Registration date
Wednesday September 8, 2010
Status
Member
Last seen
September 10, 2010

OK I cannot seem to get this to work, the range needed is I3 thru I 263 (which I think would be "I3:I263" correct?) and the colors needed are red (24?) for the passed date and yellow(35?) for the month away date. While working on this even using it just the way you have here everything stays clear, I might be just missing a simple step though.