Change event and ranges

[John Keith]

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

 

[Jim Thomlinson]

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

 

[John Keith]

Thanks Jim.
For each rng in isect...
was exactly the syntax i was looking for.

 

[Jon Peltier]

To be safe, you should probably use

For Each rng In isect.Cells

- Jon

 

[Curt]

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