Highlighti Row/ colour fill through VB Solved/Closed

Question

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.

TrowaD · Answer

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

Highlighti Row/ colour fill through VB

1 response

Subscribe To Our Newsletter!