Having learned how to determine the last row of worksheet. I now am trying to sort the worksheet (done successfully) then compare the date cell in each row to today's date. If the date cell is in the past, I want to color some cells in that row gray. Here is the code I am using but I continue to get a syntax error. I don't think I am that far off but cannot find the solution.
Sub doit()
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
Range("A" & LastRow + 1).Value = LastRow
Range("A1:R1000").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
For i = 2 To LastRow
If "Bi" < today() Then Range("Ai":"Ri").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Next
End If
End Sub
Option Explicit
Sub doit()
Dim LastRow As Long
Dim i As Long
LastRow = Range("A65536").End(xlUp).Row
Range("A" & LastRow + 1).Value = LastRow
Range("A1:R1000").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
For i = 2 To LastRow
If Range("B" & i) < Date Then
With Range("A" & i & ":R" & i).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End If
Next
End Sub
dasadler wrote:Thank you. Perfect - so the problem was that today() is not used by VBA? That DATE is used instead?
Yes, Date is the VBA equivalent of the worksheet function today().
Also, you had your Next before 'End If'
In addition, I removed the selecting and correctly indented the loop.
dasadler wrote:...I now am trying to ... compare the date cell in each row to today's date. If the date cell is in the past, I want to color some cells in that row gray...
This is very easy to do using just conditional formatting, with no code at all.
That is how I was originally doing it... along with a lot of other testing and formatting... all CF based. However, I found that as I inserted rows & deleted rows then replicated formulas, it became awkward and I found myself having to delete all CF then manually reapply the various formats.
I thought I might be able to do it with a macro but that was problematic so i concluded that with only a few rows (1000), I could easily (relative term) do the sorting, testing, and formatting with a macro by a single click. So now I am trying to build the code to do it... part of it is done - sorting then graying out the past dates. My next challenge is to expand the cell testing & formatting within the loop.
I will likely (no... certainly) return with more questions.
As Steve mentioned in your other thread, it would be better to use 'Rows.Count' instead of 65536, to ensure accuracy in versions 2007 upwards. Also, you may wish to use 'LastRow' in your sort instead of 1000. Like so:
Option Explicit
Sub doit()
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LastRow + 1).Value = LastRow
Range("A1:R" & LastRow).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
For i = 2 To LastRow
If Range("B" & i) < Date Then
With Range("A" & i & ":R" & i).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
End If
Next
End Sub
As a side note, if in future you wish to delete rows within your loop, you should loop backwards by changing:
For i = 2 To LastRow
to:
For i = LastRow To 2
This is something that took me a while to get my head around in my early days. My lounge membership has served me well.
What i meant by deleting rows is something I do manually as needed. This worksheet is a sort of schedule of paid events that contains, initially, one row per event. As I determine what other vendors and contractors I need for these events, I insert rows and use the same date & event number with one row per vendor/contractor (meaning possibly multiple rows per event). If the event is later canceled or downsized, I delete those rows no longer needed.
In this case, why would it make a difference looping backwards? I defer to your experience and will make the change... I would just like to understand the benefit/advantage. or, at least, the disadvantage of not looping backwards.
dasadler wrote:In this case, why would it make a difference looping backwards? I defer to your experience and will make the change... I would just like to understand the benefit/advantage. or, at least, the disadvantage of not looping backwards.
There is no need to loop backwards unless you are intending to delete rows within the loop. See Hans tutorial on looping backwards. I mentioned the matter as a side note, just in case.
Nathan 
