Convert a worksheet_SelectionChange to WorkBook_SheetChange macro

sparkle · #1 08-02-2014, 10:01 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro to convert text to endnote?	Orifacious	Word VBA	27	03-29-2022 02:58 PM
Macro to convert word file into pdf	cc9083	Word VBA	2	02-23-2015 01:22 AM
custom icon, undo/redo for macro, permanent macro	Rapier	Excel	0	08-05-2013 06:30 AM
Need a macro to convert all graphics to GIF format	sleake	Word VBA	3	02-10-2012 05:00 PM
Creating macro to convert/print to pdf	shabbaranks	Word	3	05-18-2011 08:59 AM