Convert a worksheet_SelectionChange to WorkBook_SheetChange macro
I have several worksheets in a workbook containing consecutive serial numbers. WS1 runs from10000 to 10999, WS 2 from 11000 to 11999 and so on. The code below highlights the selected cell and then shows the number of highlighted cells in “V1”. I could of course put this macro in each of the worksheets and it would work fine but when I try to use a Workbook_Sheetchange based on the same code it goes into a continuous loop and generates a stack overflow error. Any help telling me where I am going wrong, and setting me right would be greatly appreciated. As you will gather I am a novice at VBA.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = 3
Range("V1") = 0
Range("V1").Interior.ColorIndex = 0
For arow = 1 To 50
For acol = 1 To 20
If Cells(arow, acol).Interior.ColorIndex = 3 Then
Range("V1").Value = Range("V1").Value + 1
End If
Next acol
Next arow
End Sub
|