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
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 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
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
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
2742
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 4, 2021
460
Thanks for the feedback DevRatan.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!