Highlighting rows by date Solved/Closed

Question

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.  


Configuration: Windows Vista / Firefox 3.6.8

venkat1926 · Answer

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.

venkat1926 · Answer

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.

Highlighting rows by date

2 responses

Similar discussions

Subscribe To Our Newsletter!