VBA - adjust Worksheet Change to work across different sheets

[Jasesair]

I have this worksheet change below that works well. I'd like to amend it so it works across worksheets if possible. I'm just not sure how to do this or where to place this in the project(I'm guessing in This Workbook???). I obviously need to add the different worksheet names to each of the ranges so not certain I know how to do that properly either.

Any help would be fantastic - I'm a bit new to VBA.

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Address = Range("c12:c46").Address Then
Range("$j$2").Value = "Please Select..."

End If

End Sub

 

[Fluff]

How about

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Not Intersect(Target, Sh.Range("C12:C46")) Is Nothing Then
      Sh.Range("J2").Value = "Please Select..."
   End If
End Sub

This needs to go in the ThisWorkbook module.

 

[Jasesair]

That looks great. Where and how do I add the sheet name to the range?

 

[Fluff]

In what way?

 

[Jasesair]

The two ranges are in different sheets, but they're not referenced at all. I'm having a go but have very little idea what I'm doing! C12:C46 is in SheetA and J2 is in SheetB.

 

[Fluff]

Ok, I misunderstood. I thought you wanted the change event to work on multiple sheets.
Use this instead

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("C12:C46")) Is Nothing Then
      Sheets("SheetB").Range("J2").Value = "Please Select..."
   End If
End Sub

This needs to go in SheetA code module.

 

[Jasesair]

Thanks Fluff, that's exactly what's needed. If I've already got another sub in this SheetA code module (same first line), what do I need to do to add this one and make it work?

Sorry for all the questions - I'm learning plenty here!

 

[Fluff]

What is the existing code?

 

[Jasesair]

Woohoo, I managed to combine them. I'd missed an end if. Does this compilation look correct? Really appreciate the feedback. SheetA and SheetB are renamed now...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("$g$6").Address Then
Range("f14:f17,f21:f24").Value = "Please Select..."

End If

If Not Intersect(Target, Range("C12:C46")) Is Nothing Then
Sheets("Student Folio").Range("J2").Value = "Please Select..."

End If

End Sub

 

[Fluff]

That should be fine