Change event and ranges
 

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

Change event and ranges
 

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

Change event and ranges
 

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

Change event and ranges
 

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" wrote in message
...
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

Change event and ranges
 

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