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
|