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