VBA Code to update valiated data across multiple worksheets

[Jan1419]

Hi,

I'm very new to VBA code, was hoping for some help, I have the code below. This works perfectly in my spreadsheet with my data list on sheet 1 and my drop down lists on the sheet "Sep 17". I would like the code to work for additional sheets also, is there someone that can help me with this? Thank you in advance

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
Dim rng As Range
For count_cells = 1 To Range("B1").CurrentRegion.Rows.Count - 1
Set rng = Worksheets("Sep 17").Range("B7:AF235")
If Intersect(Target, Range("B" & count_cells + 1)) Is Nothing Then
Else
Application.EnableEvents = False
new_value = Target.Value
Application.Undo
old_value = Target.Value
Target.Value = new_value
rng.Replace What:=old_value, Replacement:=new_value
Target.Select
End If
Next count_cells
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[ranman256]

worksheet change has 2 params:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
you can use the incoming sh object

Set rng = sh.Range("B7:AF235")

 

[Jan1419]

Thank you for your response, i tried copying
Set rng = sh.Range("B7:AF235")

instead of
Set rng = Worksheets("Sep 17").Range("B7:AF235")

but it didn't work...