If date in cell is older than todays date by 1 month Then Font RED

[ipbr21054]

Hi,
On my worksheet in column H i have dates of when i quoted for a job.
My goal is to check each date with todays date & if more than 1 moth old have that rows Font vbRed.

So my setup is like this.
Headers in Row 1
Dates are all in column H
Values start Row 2 & down the page.
Cells in use are column A to L
Worksheet is called QUOTES

So when the worksheeet QUOTES is open the code will run.
The code will check the date in column H with todays date.
Any date that is older than 1 month will have that rows cells Font changed to vbRed

 

[Skyybot]

This should work. Place the code in ThisWorkbook code module of your Workbook

Option Explicit

Private Sub Workbook_Open()
Dim wsht As Worksheet, rng As Range, cell As Range, i As Integer

Set wsht = Me.Sheets("QUOTES")
Set rng = wsht.Columns("H").Cells

For Each cell In rng
    If IsEmpty(cell) Then Exit Sub
    If IsDate(cell.Value) Then
        If cell.Value < Date - 30 Then
            wsht.Rows(cell.Row).Cells.Font.Color = vbRed
        End If
    End If
Next cell
End Sub

 

[Alex Blakenburg]

I have tried the following but it has no affect at all ?

Do you see why that may be

If Sheets("QUOTES").Range("H2").Value > DateAdd("m", 1, Date) Then
Range("A2:L2").Font.Color = vbRed
End If

If you are just wanted to see how to correct your if statement. Then you can just modify it using the function Peter used in his filter suggestion:

If Sheets("QUOTES").Range("H2").Value < DateAdd("m", -1, Date) Then
    Range("A2:L2").Font.Color = vbRed
End If

 

[ipbr21054]

I like the way the code advised in post #10 works,meaning the small amount of code stops me having to use my code over & over again & just changing the row number etc.

Whilst testing it ive noticed that if i enter a date say 02/10/2024 the code deletes that row.
W£hy does it delete that row ?

All the code is supposed to do is if the date in column H is more than 1 month old change that row font to vbRed
It should still keep all the values but just change the font

 

[ipbr21054]

So this is what i have is use & works but as you can see its repetitive.
How can we write it correctly so its a small code & just works for any length of rows that are added.

Private Sub Worksheet_Activate()
If Sheets("QUOTES").Range("H2").Value < DateAdd("m", -1, Date) Then
Range("A2:L2").Font.Color = vbRed
End If

If Sheets("QUOTES").Range("H3").Value < DateAdd("m", -1, Date) Then
Range("A3:L3").Font.Color = vbRed
End If

If Sheets("QUOTES").Range("H4").Value < DateAdd("m", -1, Date) Then
Range("A4:L4").Font.Color = vbRed
End If

If Sheets("QUOTES").Range("H5").Value < DateAdd("m", -1, Date) Then
Range("A5:L5").Font.Color = vbRed
End If

If Sheets("QUOTES").Range("H6").Value < DateAdd("m", -1, Date) Then
Range("A6:L6").Font.Color = vbRed
End If

End Sub

 

[Peter_SSs]

Whilst testing it ive noticed that if i enter a date say 02/10/2024 the code deletes that row.
W£hy does it delete that row ?

There is nothing in the code in post 10 that deletes anything. If anything is being deleted then there must be some other code doing it.

 

[ipbr21054]

The olny code i have for deleting a customer is to select that customer in column A but then that code shouldnt be triggered by this code.
I will have to take a look or can you advise on my post above.

Thanks

 

[ipbr21054]

Ok so the issue i have regarding the row being deleted.
I open my usefrom complete all the fields & values sent to my worksheet.
I leave this worksheet & then come back to it & the new added entry gets deleted this is with todays date.

What i have noticed is the Filter icon is shown Orange & arrows for each column are shown.
If i click on the Filter arrow to unselect it the rows that were deleted are now shown ????

 

[Peter_SSs]

The olny code i have for deleting a customer is to select that customer in column A

If the above statement is correct then how is the userform mentioned below opened and how are the values from the userform sent to your worksheet as mentioned below?

I open my usefrom complete all the fields & values sent to my worksheet.

 

[ipbr21054]

UPDATE

Just noticed that they become hidden not deleted.
I think I will leave this as getting to in-depth.

I thought it was going to be an easy task.
First row in range, look at date in column H, if older than 1 month then that row don’t vbRed.
Next row do the same & so on.

 

[Peter_SSs]

So, doesn't the code in post 10 do what you want?


If not can you clarify ..

Just noticed that they become hidden not deleted.

If the row becomes hidden is it hidden by an AutoFilter or is it hidden by by some other code or is it hidden manually or is there yet another explanation of how it becomes hidden?