why doesnt it do what I want? (lol)

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I guess thats what they all ask.

I´m clueless.
I have a worksheet with a background colored range.
I wish to react on "selection_change" i.e. click in sheet
to see wether the clicked field has a bg-color and if so
then I want to select the complete section that is colored
but only the clicked row. So thats exactly what my routine
does BUT when I click in the most left field of the
colored section then it does not work.

Since I change the selection (by selecting the whole
colored row) I call the routine recursively again. is
there a way to avoid that?

because if I insert a (range"a1".select) before activating
the correct range returned from my subroutine then it
works - but of course it hangs recursive.

As I said, I´m clueless.
Dennis

Here comes the code.

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Set ResultRange = test(ActiveCell.Address)
' range("a1").select
ResultRange.Activate
End Sub

' ************************** subroutine ***************

Public Function test(dummy)
' *********************************************************
c = 0
Do While (c > -1)
If ActiveCell.Offset(0, -c).Interior.ColorIndex =
2 Then
Exit Do
End If
If ActiveCell.Offset(0, -c).Interior.ColorIndex = -
4142 Then
Exit Do
End If
c = c + 1
Loop


r = 0
Do While (r > -1)
If ActiveCell.Offset(0, r).Interior.ColorIndex = 2
Then
Exit Do
End If
If ActiveCell.Offset(0, r).Interior.ColorIndex = -
4142 Then
Exit Do
End If
r = r + 1 ' zähler erhöhen
Loop ' nächste zelle prüfen

If (r <> 0 Or c <> 0) Then
currrow = ActiveCell.Row
startcol = ActiveCell.Column - c + 1
endcol = ActiveCell.Column + r - 1
Set test = Range(Cells(currrow, startcol), Cells
(currrow, endcol))
Else
Set test = Range(ActiveCell.Address)
End If
End Function
 
Stop the recursion by disabling events. And use Select rather than Activate
to do a selection when you click on the leftmost cell. Now the only issue
is dealing with the error that occurs if the backgrounded cells go all the
way to column A or IV<g>.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ResultRange As Range
Set ResultRange = test(ActiveCell.Address)
Application.EnableEvents = False
ResultRange.Select
Application.EnableEvents = True
End Sub
 
Back
Top