Run a macro on a selected sheet or sheets

[ABabeNChrist]

I have a macro that is used to change a selected text to a red font, I use this method to run the macro

Code: Select all

 Private Sub Worksheet_Change(ByVal Target As Range)
    Call ColorandBold
End Sub

This works great but is there a way to have this macro only run on selected sheet.

[HansV]

The Worksheet_Change event belongs to one specific sheet, and it occurs only when the user changes one or more cells in that sheet, i.e. when the sheet is the active sheet. So what exactly is your problem?

[StuartR]

If you are really confused then you could check the value of Target.Parent.Name, but as Hans has pointed out, your Macro can only be associated with one worksheet.

[stuck]

...only run on selected sheet.

Hans / Stuart, perhaps the desire is to have the macro only work one one specific worksheet in the workbook, i.e. have macro work when Steet1 is active but NOT when any other is the active sheet?

If so then presumably a test on the active sheet name prior to the Call is all that is required?

Ken

[HansV]

But the Worksheet_Change event of Sheet1 will only occur if the user changes a cell on Sheet1; this normally requires Sheet1 to be the active sheet: the user can't edit a cell on a sheet that is not the active sheet.
The only exception is when the user selects multiple sheets and edits a cell. In that case, the Worksheet_Change event will occur for each selected sheet. But I would have thought that that would be desirable...

[stuck]

But the Worksheet_Change event of Sheet1 will only occur if the user changes a cell on Sheet1; this normally requires Sheet1 to be the active sheet

Err..., yes. This obvious point came to me about 30 seconds after I clicked submit.

Ken

[StuartR]

Alternatively you could use an application event such as

Code: Select all

Private Sub App_SheetChange(ByVal sh As Object, ByVal Target As Range)

but as you can see this returns the sheet as an object, as well as the target.

[ABabeNChrist]

Thank you everyone
You are right Hans it only happens when selecting mutable sheets, but sometimes when I use mutable sheets, I have one sheet in question that I wish to avoid this code to run on because it is a state document, and the state fronds on alterations beyond their format. I have no problem with any of the other sheets, just 1

[HansV]

Simply don't call ColorAndBold from the Worksheet_Event procedure of that particular sheet.

[ABabeNChrist]

You got it thank you Hans
I also used Conditional Formatting on selected sheet within selected cells of concern to keep text to original format, just in case