Highlight entire row if date exceeded 3 days

[coolman]

Hi there

I am trying to use vba to check each cells in the column if the date has exceeded 3 days. If so the whole row will be highlighted red.

Here is my syntax

If Range("a:a").value < Range("a:a").value + 3 then

Range("a:a")EntireRow.Interior.ColorIndex = 8

End If

However I could not get the program to work. Are there anyone who can advice me on this?

 

[JLGWhiz]

try this

Sub t()
Range("A:A").AutoFilter 1, "<" & Date - 3
Range("A2", Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.Color = vbYellow
ActiveSheet.AutoFilterMode = False
End Sub

 

[Rick Rothstein]

try this

Sub t()
Range("A:A").AutoFilter 1, "<" & Date - 3
Range("A2", Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.Color = vbYellow
ActiveSheet.AutoFilterMode = False
End Sub

I would point out that if no dates in Column A meet the required date criteria, then the first row will become colored yellow with your code.

 

[coolman]

Is there any way to prevent the first row to become yellow ? Also would it be possible to have a simpler syntax to perform the comparison as I am at the beginner level

 

[mandukes]

Hi
There you go...

Try this..

' Tested and working..

Sub changeCol()


Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value <> "" Then
If Range("A" & i).Value < Date - 3 Then
Range("A" & i).EntireRow.Interior.Color = vbYellow
Else:
Range("A" & i).EntireRow.Interior.Color = vbWhite
End If
End If
Next i


End Sub

 

[mandukes]

Is there any way to prevent the first row to become yellow ? Also would it be possible to have a simpler syntax to perform the comparison as I am at the beginner level

Hi , Try my code above mate, it works as you desire..

:D

 

[coolman]

Hi there

Thanks for the syntax.

I would like to clarify this syntax line:
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

what is syntax trying to perform?

Also for this syntax: If Range("A" & i).Value < Date - 3, if i change it to weekdays, do i have to use dateadd to perform it?

Hi
There you go...

Try this..

' Tested and working..

Sub changeCol()


Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value <> "" Then
If Range("A" & i).Value < Date - 3 Then
Range("A" & i).EntireRow.Interior.Color = vbYellow
Else:
Range("A" & i).EntireRow.Interior.Color = vbWhite
End If
End If
Next i


End Sub

 

[mandukes]

Hi there

Thanks for the syntax.

I would like to clarify this syntax line:
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

what is syntax trying to perform?

Also for this syntax: If Range("A" & i).Value < Date - 3, if i change it to weekdays, do i have to use dateadd to perform it?

1). It would pull last used row number.
2). I tried with weekday function but it didn't work.

 

[JLGWhiz]

I would point out that if no dates in Column A meet the required date criteria, then the first row will become colored yellow with your code.

this shoud fix that

Sub t()
Range("A:A").AutoFilter 1, "<" & Date - 3
If ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count = 1 Then
    Range("A2", Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Interior.Color = vbYellow
End If
ActiveSheet.AutoFilterMode = False
End Sub

 

[coolman]

For this syntax line: If Range("A" & i).Value <> "" Then

It produce an error 440 message when i tried to run the program.

Is there any way to resolve the error?

1). It would pull last used row number.
2). I tried with weekday function but it didn't work.