Change event - checking dates

[Ian]

I have a spreadsheet where the user enters dates into cells within a
range.

I need to check that the date is within a 2 year before and after the
current time.

I have worked out the following code to do the job. It works fine
if I knock out the: Target = Range("matrix")

I don't want the code to work on cells outside the range("matrix").
I also am trying to get the code to 'see' an empty cell and not
colour it in if the cell is empty, but previously contained an out of
range (2 year) date.

Why does the code not work when the: Target = Range("matrix") is
made available?

Thanks,

Ian,




Private Sub Worksheet_Change(ByVal Target As Range)

over730days = Now() + 730
under730days = Now() - 730

Target = Range("matrix")

For Each cell In Target
If ((cell.Value > over730days) Or (cell.Value < under730days)) Then
cell.Interior.ColorIndex = 8

Next cell

End Sub

[Rowan]

Maybe two options both of which will mean this event will run every time
any change is made on the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
over730days = Now() + 730
under730days = Now() - 730
set Target = Range("matrix")
For Each cell In Target
If ((cell.Value > over730days) Or (cell.Value < under730days)) Then
cell.Interior.ColorIndex = 8
Next cell
End Sub

or:

Private Sub Worksheet_Change(ByVal Target As Range)
over730days = Now() + 730
under730days = Now() - 730
For Each cell In range("Matrix")
If ((cell.Value > over730days) Or (cell.Value < under730days)) Then
cell.Interior.ColorIndex = 8
Next cell
End Sub

But you would probably be a whole lot better off doing this with
conditional formatting which would take away the need for any vba.

Hope this helps
Rowan

Ian wrote:
> I have a spreadsheet where the user enters dates into cells within a
> range.
>
> I need to check that the date is within a 2 year before and after the
> current time.
>
> I have worked out the following code to do the job. It works fine
> if I knock out the: Target = Range("matrix")
>
> I don't want the code to work on cells outside the range("matrix").
> I also am trying to get the code to 'see' an empty cell and not
> colour it in if the cell is empty, but previously contained an out of
> range (2 year) date.
>
> Why does the code not work when the: Target = Range("matrix") is
> made available?
>
> Thanks,
>
> Ian,
>
>
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> over730days = Now() + 730
> under730days = Now() - 730
>
> Target = Range("matrix")
>
> For Each cell In Target
> If ((cell.Value > over730days) Or (cell.Value < under730days)) Then
> cell.Interior.ColorIndex = 8
>
> Next cell
>
> End Sub