Highlight a Row based on a date

[armesca]

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


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

 

[pbaldy]

Did you mean to include the entire comparison within the first DateValue, or is this what you wanted:

If (r.Cells(, 13) = "OPEN") And DateValue(r.Cells(, 6).Value) < DateValue(Me.txt_cfodate) Then

 

[armesca]

Yes, What you wrote is what I want and is correct, still getting a data type mismatch. I am assuming because DATEVALUE is for use on a string?

 

[pbaldy]

I'm pretty sure the DataValue() function can handle a date or a string, at least in Access. Might the cell be empty? Not sure it would like that.

 

[armesca]

Dim r as range


I think this is problem with my datevalue calculation. Any idea how to get around it? Can I set r to a string instead of a range?

 

[pbaldy]

To be honest, I don't work within Excel much. When I automate it from Access, I do this type of thing:

  Dim R                As Long    'rows
  Dim C                As Integer    'columns

  xl.Cells(R, C) = Whatever
  R = R + 1

Where xl is the Excel application object. I don't think I've ever used Range in code. In the above scenario, I'd likely be testing for an empty Excel cell as a went down the worksheet.

 

[armesca]

Thanks for the help. Unfortunately you can't set a variable as long or an integer if it is part of a For..Next Statement, it has to be an object or variant.

 

[pbaldy]

I'm not sure what you're saying. You can certainly have an Integer or Long in the For/Next, like

For x = 1 To 10
   ...
   x = x + 1
Next x

where x is declared as Long or Integer. In your case, if you're trying to go down then entire worksheet, I'd do this type of thing:

Do Until xl.Cells(R, 1) = ""
  ...
  R = R + 1
Loop

Which tests that column A has something in it.

 

[armesca]

Okay. I am still a little new to VBA? Would inserting a do until statement get rid of my data mismatch problem i am having here: If (r.Cells(, 13) = "OPEN") And DateValue(r.Cells(, 6).Value) < DateValue(Me.txt_cfodate)

Thanks again for your help

 

[pbaldy]

I would think so, if using Range was the problem. Can you post the worksheet (or both if you're doing this from Access)?

 

[Brianwarnock]

I am used to working in native EXCEL not via ACCESS but I believe this is where your error is

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 you look at the value of r at the point of failure I believe that it will contain the value of your first cell.

All I would do in native Excel is something like this

Dim lastrow As Long
Dim r As Long

lastrow = ActiveSheet.UsedRange.Rows.Count

For r = 1 To lastrow

You have your lastrow , although have never done it that way , so you can do the same.

Brian

 

[Brianwarnock]

Thanks for the help. Unfortunately you can't set a variable as long or an integer if it is part of a For..Next Statement, it has to be an object or variant.

Possibly because you are using a range object.

Brian

 

[Brianwarnock]

Although I wouldn't do it this way i got curious and did a little test, and now I don't think that is the problem!!

This works fine

Dim lastrow As Long
Dim myrange As Range

lastrow = ActiveSheet.UsedRange.Rows.Count

Set myrange = Range(Cells(1, 1), Cells(lastrow, 1))
Set myrange = myrange.Rows("1:" & lastrow)

For Each r In myrange.Rows
If r.Cells(, "c") = "" Or r.Cells(, "C").Value = " " Then
    r.Interior.ColorIndex = 5
End If

Next r

I'm now puzzled.

Brian

 

[armesca]

If r.Cells(, "c") = "" Or r.Cells(, "C").Value = " " Then


In this line of code, are you referring to column C? Can you intercahnge numbers and letters for columns?

I have my code running now, but it doesn't actually highlight anything. Also, I implemented, lastrow = ActiveSheet.UsedRange.Rows.Count and did a msgbox for lastrow and it gives me an the same arbitrary incorrect number for every sheet. Any ideas??



For Each objws In objwb.Worksheets
If Right(objws.Name, 6) = "Detail" Then
objws.Activate

lastrow = ActiveSheet.UsedRange.Rows.Count
MsgBox lastrow
For r = 1 To lastrow

If (Cells(r, 13).Value = "OPEN") And (Cells(r, 6).Value) < (Me.txt_cfodate) Then
Rows(r).Interior.ColorIndex = 3

End If

Next r

End If
Next objws

 

[Brianwarnock]

Ok now messed with Datevalue and it handles Nulls , strings and Dates in a comparison.

I cannot see the problem with your code, except I don't think that there should be a ) after "OPEN"

Brian

 

[armesca]

If (Cells(r, 13).Value) = "OPEN" And DateValue(Cells(r, 6).Value) < DateValue(Me.txt_cfodate)

When I put datevalue in, I get a type mismatch error, it is because of the bolded area above, so frustrating!!

 

[Brianwarnock]

I went back to my tests and found that I had mage a silly mistake and Datevalue does not accept Nulls so you ,may have that problem,
put
Debug.Print Cells(r, 6).Value
just prior to that code and see in the immediate window what is in the cell when it fails

Brian