How to Get Date of Each Cell....

[Guest]

I need to highlight each cell in a column that satisfies anything earlier
than the current date+1 (e.g. anything earlier than 2006-06. June '06). They
have to be processed as strings, thus the fist two lines.

s_current = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "yyyy-mm")
s_past = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy-mm")
Application.Goto Reference:="MP_Start"
myMPStartColumn = ActiveCell.Column
Set rng = ActiveCell.EntireRow
For Each cell In rng
If s_current >= s_past Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next

I know this isn't doing anything (I need to do something with "cell"). How
to I evalutate each cell in the colume and compare it against the current
date, thus being able to highlight, given that criteria?

Please help!

Thanks,
Mark

 

[Brian Taylor]

Have you tried conditional formatting?

"formula is" =DateValue(A1)<(today()+1)

 

[Guest]

Brian,

I get an error when i enter this:

s_current = Format(DateSerial(Year(Date), Month(Date), Day(Date)),
"yyyy-mm")
s_past = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy-mm")
Application.Goto Reference:="MP_Start"
myMPStartColumn = ActiveCell.Column
Set rng = ActiveCell.EntireRow
For Each cell In rng
If DateValue(cell) <= s_past Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next

I think we're getting closer. I really really appreciate your help!!!

-m

 

[Guest]

I get a type mismatch error. Is this because of the string thing?

Thanks,
Mark

 

[Brian Taylor]

Conditional formatting would be much simpler than writing code. Have
you already tried that option?

You would need to change your code a little to make it work. Here is
some untested code to take a look at:

Sub test()
Set rng = ActiveCell.EntireRow
For Each Cell In rng
If DateValue(Cell.Value) <= (Now() - 1) Then
With Cell.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next
End Sub

 

[Guest]

conditional formatting it is! it works this way, but it's weird because i
can't change the cells back to no color... even manually.

is this a side affect of running conditional formatting via macro?

thanks,
mark

 

[Brian Taylor]

You should be able to change the cells back to no fill. It should not
be a permanent fill change. The only thing that would keep you from
changing the cell color back is if you had put the procedure in the
selection change event. In which it would keep on applying the change
everytime you select a new cell.