Color cell when date is more than 40 days

[Sha]

Hi

this is the code I have thus far.


Sub HighlightCells()

Dim dtrg As Range 'Date in Col J
Dim dtCell As Range 'Date Cell
Set dtrg = Range("J1:J3000") ' Date Range

For Each dtCell In dtrg.Cells
If dtCell.Value > dtCell.value + 40 Then
dtCell.Interior.ColorIndex = 3
End If
Next dtCell


End Sub

 

[MARK858]

You are testing the cell against itself and so it can never be greater than itself +40.
If you are comparing it to todays date then it is

If dtCell.Value > Date + 40 Then

Or older than 40 days

If dtCell.Value < Date - 40 Then

 

[Sha]

Hi,

The cell is still not highlighted... do need to set the col range to date like what I have below, cause this does not work either.

Sub HighlightCells()

Dim dtrg As Date: Set dtrg = Range("J1:J3000")
Dim dtCell As Range

For Each dtCell In dtrg.Cells
If dtCell.Value < Date - 40 Then
dtCell.Interior.ColorIndex = 3

End If
Next dtCell

End Sub

 

[MARK858]

Please see the edited code in my post if you are highlighting dates older than 40 days

 

[Sha]

Apologies there was a typo that I edited.

 

[MARK858]

Is it working for you now? I assume that your "typo" was declaring dtrg as Date rather than Range?

 

[Sha]

still doesn't seem to be working....

 

[MARK858]

What are you trying to highlight exactly? Dates older than 40 days ago or more recent than 40 days ago?

 

[Sha]

HIghlighting cells which are older than 40 days.

ie if col j has a date (01/10/2020) it should be hightlighted if today was 10/11/2020. however, i do not wish to use the today() function

 

[MARK858]

The code below does that for me

Sub HighlightCells()

    Dim dtrg As Range: Set dtrg = Range("J1:J3000")
    Dim dtCell As Range

    For Each dtCell In dtrg.Cells
        If dtCell.Value <> "" And dtCell.Value < Date - 40 Then _
           dtCell.Interior.ColorIndex = 3
    Next dtCell

End Sub

If it doesn't for you...
a) where have you placed the code
b) how are you running it
c) is the same sheet active
d) if you place the formula

=ISNUMBER(J1)

in the first row of an empty column and drag down do you get all TRUE's or do you get some that are FALSE