VBa automatically run macro if cell changes

[ljkennedy]

Hi all,
I want to make the spread sheet automatically run this macro code when a number is inputted into column B
what code do i need to add to it
Sub Ratio_BC()


Dim Lastrow As Long


Application.ScreenUpdating = False


Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("D2:D" & Lastrow).Formula = "=IF(ISNUMBER(--MID($B:B,SEARCH(""241"",$B:B),3)),MID($B:B,18,10))"


Application.ScreenUpdating = True


End Sub

thanks

 

[Trebor76]

Try this event macro (i.e. right click on the tab you want the code to run on and from the short cut menu select View Code and paste the following code) on the sheet in question:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lngLastRow As Long

    If Target.Column = 2 Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
                lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
                Range("D2:D" & lngLastRow).Formula = "=IF(ISNUMBER(--MID($B:B,SEARCH(""241"",$B:B),3)),MID($B:B,18,10))"
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If

End Sub

Have you tested the formula as I'm I don't think SEARCH or MID don't work for entire columns?

Robert