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

charlesdh · #2 08-02-2014, 11:09 AM

Hi,

The reason you go into a continuous look is that each time a chane is made it will fire the macro.
I think you need "Application.EnableEvents = False" as indicated in the code. However, I'm not completely
sure if the placement is correct.
Also please use "Code" tags when you post a code.

Code:

 "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
Application.EnableEvents = False
    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
Application.EnableEvents = True
End Sub

sparkle · #3 08-03-2014, 02:26 AM

Hi Charlesdh
That's super, works a treat. Point taken re code tags.
Thanks again

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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