Highlighting rows by date

Solved/Closed
bryanfarley Posts 3 Registration date Wednesday September 8, 2010 Status Member Last seen September 10, 2010 - Sep 8, 2010 at 02:52 PM
bryanfarley Posts 3 Registration date Wednesday September 8, 2010 Status Member Last seen September 10, 2010 - Sep 10, 2010 at 10:13 AM
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 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 9, 2010 at 09:23 PM
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.
bryanfarley Posts 3 Registration date Wednesday September 8, 2010 Status Member Last seen September 10, 2010
Sep 10, 2010 at 10:13 AM
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.
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 9, 2010 at 06:31 AM
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.
bryanfarley Posts 3 Registration date Wednesday September 8, 2010 Status Member Last seen September 10, 2010
Sep 9, 2010 at 10:31 AM
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.
0