Change text in column

[adam]

Hi anyone,

I'm using the following code to change text on column I of the sheet Orders when the user changes the value in cell O9 of the active sheet by double clicking.

My question of concern is how to change the code so that when the user changes the text in cell O6 and runs the macro; the text of the sheet mentioned in the code gets changed.

Meaning the code to be changed as

Sub ChangeText()
............
End Sub

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim FoundSN As Range, FirstFound As String, strInOut As String

    Application.ScreenUpdating = False
    
    If Not Intersect(Target, Range("O9")) Is Nothing Then
    
        If Target.Value = "OUT" Then
            Target.Value = "IN"
            strInOut = "IN"
        Else
            Target.Value = "OUT"
            strInOut = "OUT"
        End If
        
        With Sheets("Orders")
        
            Set FoundSN = .Range("B:B").Find(Range("O6"))
            
            If Not FoundSN Is Nothing Then
                FirstFound = FoundSN.Address
                Do
                    FoundSN.Offset(, 7).Value = strInOut
                    Set FoundSN = .Range("B:B").FindNext(FoundSN)
                Loop Until FoundSN.Address = FirstFound
            Else
                MsgBox "Coundn't match serial number."
            End If
        End With
    End If
    
    Application.ScreenUpdating = True
    
End Sub

I hope I've made my question clear.

Any help on this would be kindly appreciated.

[HansV]

Like this?

Code: Select all

Sub ChangeText()
    Dim FoundSN As Range, FirstFound As String, strInOut As String
    Application.ScreenUpdating = False
    strInOut = Range("O9").Value
    With Sheets("Orders")
        Set FoundSN = .Range("B:B").Find(Range("O6"))
        If Not FoundSN Is Nothing Then
            FirstFound = FoundSN.Address
            Do
                FoundSN.Offset(, 7).Value = strInOut
                Set FoundSN = .Range("B:B").FindNext(FoundSN)
            Loop Until FoundSN.Address = FirstFound
        Else
            MsgBox "Couldn't match serial number." ' corrected spelling error
        End If
    End With
    Application.ScreenUpdating = True
End Sub

[adam]

Exactly. Thanks for the help Hans. I do really appreciate your help.