An area on my worksheet is used to build a VLookup table of holidays. I have
a button that will re-sort the table. I have the code bleow that will change
the color of the cell with the date when it is entered. Also the button
changes text & color based on if a new sort will be needed...

I am getting a type mismatch error when I select an area (that intersects
one of my date cells). How can I handle this to prevent errors? or how do I
test for erasing an area and only go into the change color code when I am
processing the cell(s) of an area that intersect?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, ActiveSheet.Range("e6:e50"))
If Not isect Is Nothing Then
If Len(Target.Value) > 0 Then
Target.Interior.ColorIndex = 3 'Cell background red
Set_SortButton ("Red")
Target.Select
Else
Target.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub

--
Regards,
John

I'm not too sure that I followed the question 100% but give ths a wirl...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Dim rng As Range

Set isect = Application.Intersect(Target, ActiveSheet.Range("e6:e50"))
If Not isect Is Nothing Then
For Each rng In isect
If Len(rng.Value) > 0 Then
Target.Interior.ColorIndex = 3 'Cell background red
'Set_SortButton ("Red")
Target.Select
Else
rng.Interior.ColorIndex = xlColorIndexNone
End If
Next rng
End If
End Sub
--
HTH...

Jim Thomlinson


"John Keith" wrote:

> An area on my worksheet is used to build a VLookup table of holidays. I have
> a button that will re-sort the table. I have the code bleow that will change
> the color of the cell with the date when it is entered. Also the button
> changes text & color based on if a new sort will be needed...
>
> I am getting a type mismatch error when I select an area (that intersects
> one of my date cells). How can I handle this to prevent errors? or how do I
> test for erasing an area and only go into the change color code when I am
> processing the cell(s) of an area that intersect?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim isect As Range
> Set isect = Application.Intersect(Target, ActiveSheet.Range("e6:e50"))
> If Not isect Is Nothing Then
> If Len(Target.Value) > 0 Then
> Target.Interior.ColorIndex = 3 'Cell background red
> Set_SortButton ("Red")
> Target.Select
> Else
> Target.Interior.ColorIndex = xlColorIndexNone
> End If
> End If
> End Sub
>
> --
> Regards,
> John

Thanks Jim.
For each rng in isect...
was exactly the syntax i was looking for.
--
Regards,
John


"Jim Thomlinson" wrote:

> I'm not too sure that I followed the question 100% but give ths a wirl...
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim isect As Range
> Dim rng As Range
>
> Set isect = Application.Intersect(Target, ActiveSheet.Range("e6:e50"))
> If Not isect Is Nothing Then
> For Each rng In isect
> If Len(rng.Value) > 0 Then
> Target.Interior.ColorIndex = 3 'Cell background red
> 'Set_SortButton ("Red")
> Target.Select
> Else
> rng.Interior.ColorIndex = xlColorIndexNone
> End If
> Next rng
> End If
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "John Keith" wrote:
>
> > An area on my worksheet is used to build a VLookup table of holidays. I have
> > a button that will re-sort the table. I have the code bleow that will change
> > the color of the cell with the date when it is entered. Also the button
> > changes text & color based on if a new sort will be needed...
> >
> > I am getting a type mismatch error when I select an area (that intersects
> > one of my date cells). How can I handle this to prevent errors? or how do I
> > test for erasing an area and only go into the change color code when I am
> > processing the cell(s) of an area that intersect?
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim isect As Range
> > Set isect = Application.Intersect(Target, ActiveSheet.Range("e6:e50"))
> > If Not isect Is Nothing Then
> > If Len(Target.Value) > 0 Then
> > Target.Interior.ColorIndex = 3 'Cell background red
> > Set_SortButton ("Red")
> > Target.Select
> > Else
> > Target.Interior.ColorIndex = xlColorIndexNone
> > End If
> > End If
> > End Sub
> >
> > --
> > Regards,
> > John

To be safe, you should probably use

For Each rng In isect.Cells

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"John Keith" <(E-Mail Removed)> wrote in message
news:EAE5EDEC-F9E1-42EC-85EF-(E-Mail Removed)...
> Thanks Jim.
> For each rng in isect...
> was exactly the syntax i was looking for.
> --
> Regards,
> John
>
>
> "Jim Thomlinson" wrote:
>
>> I'm not too sure that I followed the question 100% but give ths a wirl...
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Dim isect As Range
>> Dim rng As Range
>>
>> Set isect = Application.Intersect(Target, ActiveSheet.Range("e6:e50"))
>> If Not isect Is Nothing Then
>> For Each rng In isect
>> If Len(rng.Value) > 0 Then
>> Target.Interior.ColorIndex = 3 'Cell background red
>> 'Set_SortButton ("Red")
>> Target.Select
>> Else
>> rng.Interior.ColorIndex = xlColorIndexNone
>> End If
>> Next rng
>> End If
>> End Sub
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "John Keith" wrote:
>>
>> > An area on my worksheet is used to build a VLookup table of holidays.
>> > I have
>> > a button that will re-sort the table. I have the code bleow that will
>> > change
>> > the color of the cell with the date when it is entered. Also the
>> > button
>> > changes text & color based on if a new sort will be needed...
>> >
>> > I am getting a type mismatch error when I select an area (that
>> > intersects
>> > one of my date cells). How can I handle this to prevent errors? or
>> > how do I
>> > test for erasing an area and only go into the change color code when I
>> > am
>> > processing the cell(s) of an area that intersect?
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > Dim isect As Range
>> > Set isect = Application.Intersect(Target,
>> > ActiveSheet.Range("e6:e50"))
>> > If Not isect Is Nothing Then
>> > If Len(Target.Value) > 0 Then
>> > Target.Interior.ColorIndex = 3 'Cell background red
>> > Set_SortButton ("Red")
>> > Target.Select
>> > Else
>> > Target.Interior.ColorIndex = xlColorIndexNone
>> > End If
>> > End If
>> > End Sub
>> >
>> > --
>> > Regards,
>> > John

first time for multiple triggers.
this seems to be what I want. Can it be modified to specify conditions for
many cells in a row to run event? do not know if you can have multiple
conditions for event trigger
thanks

"John Keith" wrote:

> Thanks Jim.
> For each rng in isect...
> was exactly the syntax i was looking for.
> --
> Regards,
> John
>
>
> "Jim Thomlinson" wrote:
>
> > I'm not too sure that I followed the question 100% but give ths a wirl...
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim isect As Range
> > Dim rng As Range
> >
> > Set isect = Application.Intersect(Target, ActiveSheet.Range("e6:e50"))
> > If Not isect Is Nothing Then
> > For Each rng In isect
> > If Len(rng.Value) > 0 Then
> > Target.Interior.ColorIndex = 3 'Cell background red
> > 'Set_SortButton ("Red")
> > Target.Select
> > Else
> > rng.Interior.ColorIndex = xlColorIndexNone
> > End If
> > Next rng
> > End If
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "John Keith" wrote:
> >
> > > An area on my worksheet is used to build a VLookup table of holidays. I have
> > > a button that will re-sort the table. I have the code bleow that will change
> > > the color of the cell with the date when it is entered. Also the button
> > > changes text & color based on if a new sort will be needed...
> > >
> > > I am getting a type mismatch error when I select an area (that intersects
> > > one of my date cells). How can I handle this to prevent errors? or how do I
> > > test for erasing an area and only go into the change color code when I am
> > > processing the cell(s) of an area that intersect?
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim isect As Range
> > > Set isect = Application.Intersect(Target, ActiveSheet.Range("e6:e50"))
> > > If Not isect Is Nothing Then
> > > If Len(Target.Value) > 0 Then
> > > Target.Interior.ColorIndex = 3 'Cell background red
> > > Set_SortButton ("Red")
> > > Target.Select
> > > Else
> > > Target.Interior.ColorIndex = xlColorIndexNone
> > > End If
> > > End If
> > > End Sub
> > >
> > > --
> > > Regards,
> > > John