Highlighti Row/ colour fill through VB

Solved/Closed
DevRatan
Posts
2
Registration date
Thursday May 23, 2013
Status
Member
Last seen
May 28, 2013
- May 23, 2013 at 05:35 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- May 28, 2013 at 09:14 AM
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

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
May 27, 2013 at 10:48 AM
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
0
DevRatan
Posts
2
Registration date
Thursday May 23, 2013
Status
Member
Last seen
May 28, 2013

May 28, 2013 at 05:19 AM
Thanks Heaps Trowa for your help , code tested. all okay
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
May 28, 2013 at 09:14 AM
Thanks for the feedback DevRatan.
0