Highlighti Row/ colour fill through VB

Solved/Closed
Report
Posts
2
Registration date
Thursday May 23, 2013
Status
Member
Last seen
May 28, 2013
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,

I am after a code which enables me to highlight/ colour code a row on basis of expiry date and update every time I open that exel file.

for example:
Colm A Colm B Colm C Colm D

x-info-X x-info-X x-info-X Expiry Date

30 days to expiry , row colour fill yellow
7 days to expiry, colour fill orange
expired , colour fill red

Please help, Thanking you in anticipation.

1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi DevRatan,

When the expiry date is 27-5 and today is 27-5, has it been expired now or tomorrow?

Because of this question you might have to change the numbers (the ones behind Date) in the code.
Private Sub Workbook_Open()
Dim lRow As Integer, MySh As String
MySh = "Blad1" 'Change sheetname to match yours
lRow = Sheets(MySh).Range("D" & Rows.Count).End(xlUp).Row

For Each cell In Range("D2:D" & lRow)
    If cell.Value - Date <= 0 Then cell.EntireRow.Interior.ColorIndex = 3
    If cell.Value - Date > 0 Then cell.EntireRow.Interior.ColorIndex = 45
    If cell.Value - Date > 7 Then cell.EntireRow.Interior.ColorIndex = 6
Next cell

End Sub

Paste the code under ThisWorkbook after hitting Alt+F11.

Best regards,
Trowa
Posts
2
Registration date
Thursday May 23, 2013
Status
Member
Last seen
May 28, 2013

Thanks Heaps Trowa for your help , code tested. all okay
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Thanks for the feedback DevRatan.