#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Hi Charlesdh
That's super, works a treat. Point taken re code tags. Thanks again |
|
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 |