How to Get Date of Each Cell....

[mvyvoda]

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)

[mvyvoda]

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

"Brian Taylor" wrote:

> Have you tried conditional formatting?
>
> "formula is" =DateValue(A1)<(today()+1)
>
>

[mvyvoda]

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

Thanks,
Mark

"mvyvoda" wrote:

> 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
>
> "Brian Taylor" wrote:
>
> > Have you tried conditional formatting?
> >
> > "formula is" =DateValue(A1)<(today()+1)
> >
> >

[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

[mvyvoda]

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" wrote:

> 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
>
>

[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.