Solved: run automatically each time the cell 'B5' changing information.

[marreco]

Hi.
This is a common code, I need to run automatically each time the cell 'B5' changing information.

so I need to pass the routine Private Sub Worksheet_Change.

can anyone help me?

Thank you!
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tc As Long, Day As Long
Day = Range("B5")
Tc = Application.WorksheetFunction.Match(Day, Range("d5:ag5"), 0)
For i = 8 To 149
Cells(i, Tc + 3) = Range("B" & i)
Next
End Sub

[Paul_Hossler]

I'd do something like this


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tc As Long, Day As Long, i As Long

If Intersect(Target, Range("B5")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Day = Range("B5").Value

Tc = -1
On Error Resume Next
Tc = Application.WorksheetFunction.Match(Day, Range("d5:ag5"), 0)
On Error GoTo 0

If Tc <> -1 Then
For i = 8 To 149
Cells(i, Tc + 3) = Range("B" & i)
Next
End If
Application.EnableEvents = True
End Sub


Paul

[marreco]

Hi.

I'll test but still really want to thank you for your help, thank you very much thank you!

[marreco]

I am very grateful for helping me!

was great!!

thank you!