Thread: Higlight a row based on a date

I am trying to highlight a row based on a date. I am tryin to use the DATEVALUE on a range field and am getting a data type mismatch. Any ideas on how to restructure my code to get around this? Thanks

Dim r as range
Dim myrange as range

For Each objws In objwb.Worksheets
If Right(objws.Name, 6) = "Detail" Then
'MsgBox objWS.Name
'ws.ResetAllPageBreaks
objws.Activate
lastrow = objws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
'LastCol = objws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

Set myrange = objws.Range(objws.Cells(3, 1), objws.Cells(lastrow, 20))
Set myrange = myrange.Rows("1:" & myrange.Rows.Count)
For Each r In myrange.Rows
If (r.Cells(, 13) = "OPEN") And DateValue(r.Cells(, 6).Value < DateValue(Me.txt_cfodate)) Then
r.Interior.ColorIndex = 3
r.Cells(, 3).NumberFormat = "$#,##0"
r.Cells(, 3).HorizontalAlignment = xlRight
r.Cells(, 4).NumberFormat = "$#,##0"
r.Cells(, 4).HorizontalAlignment = xlRight
r.Cells(, 5).NumberFormat = "$#,##0"
r.Cells(, 5).HorizontalAlignment = xlRight
r.Cells(, 6).NumberFormat = "$#,##0"
r.Cells(, 6).HorizontalAlignment = xlRight
r.Cells(, 7).NumberFormat = "$#,##0"
r.Cells(, 7).HorizontalAlignment = xlRight
r.Cells(, 8).NumberFormat = "$#,##0"
r.Cells(, 8).HorizontalAlignment = xlRight
End If

Next r
i = i + 1

queryname = "A1:T" & lastrow
objws.PageSetup.PrintArea = queryname

End If
Next objws

Hello armesca,

Welcome to the Forum!

This is probably the cause of the your problem...

DateValue(Me.txt_cfodate)

If the text box is in a UserForm then this definitely the cause. The reason is the "Me" keyword would refer back to the worksheet and not the UserForm.If it is on a UserForm, you will need to qualify the reference with the UserForm's name and the UserForm must be loaded into memory when this code runs or you will encounter another error.

UserForm Reference
Change the name of the UserForm to what you are using.

DateValue(UserForm1.txt_cfodate)