Highlight date

[billandrew]

Hello all

today I am trying to highlight row if less than today

this is the code I am using.

Could someone provide some explanation

Thank You

Sub highlightrow()


Dim i As Long
Dim lastrow As Long
Dim cell As Range

lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).Row


For Each cell In Range("D2:D" & lastrow)



If Cells(i, "D").Value < Date Then
Cells(i, 1).Resize(1, 5).Interior.Color = RGB(253, 249, 215)
Cells(i, 1).Resize(1, 5).Font.Bold = True

End If
Next

End Sub

 

[Joe4]

This issue is that you are looping through the cells in a range, but referencing "i" (which you are not even using) in your loop.
So it would have to look something like:

[COLOR=#333333]For Each cell In Range("D2:D" & lastrow)[/COLOR]

[COLOR=#333333]    If cell.Value < Date Then[/COLOR]
[COLOR=#333333]        cell.Resize(1, 5).Interior.Color = RGB(253, 249, 215)[/COLOR]
[COLOR=#333333]        cell.Resize(1, 5).Font.Bold = True[/COLOR]
[COLOR=#333333]    End If
[/COLOR]
[COLOR=#333333]Next[/COLOR]

However, you don't need VBA code to do this. It can be done pretty easily with Conditional Formatting.
Just select the entire range that you want to apply it to, and enter the following CF formula (assuming that you are starting in row 2):

=$D2 < Today()

and choose your highlighting color and font attribute.

 

[billandrew]

looking to utilize with code, there are other processes happening.

 

[JoeMo]

Just a heads up - your lastrow is at the bottom of the sheet. Try changing this:

lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).Row

to this

lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

 

[Joe4]

looking to utilize with code, there are other processes happening.

Did you try the code changes I made in my last post?

I will still recommend using code to set up the Conditional Formatting, as that will be much more efficient than using loops.
Loops are notoriously inefficient and can slow your code down. Sometimes, you need to use them, but if there are viable alternatives, it is usually advisable to use those.

 

[billandrew]

So obvious. Thank You very much