Reset the value to C in column C if specific text is found "ar"

[hsolanki]

Good Morning everyone

i was wondering if anyone could assist me, someone has kindly written the below code however it doesn't change all the Capital S and lowercase s to C if and when "ar" is entered or found in column C.

Private Sub Worksheet_Calculate()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("LogDetails")
Dim Cell As Range

If ws Is Nothing Then
    Exit Sub 'possible way of handing no worksheet was set
End If
If Sheets("LogDetails").Visible = xlSheetVisible Then
Sheets("LogDetails").Visible = xlSheetVeryHidden
Else
Sheets("LogDetails").Visible = xlSheetVisible
End If
  Sheets("LogDetails").Select

Range("C3:C10000").Select
Set Cell = Selection.Find(What:="AR", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Cell Is Nothing Then
    MsgBox "Did not found AR"

Else
    Columns("C").Replace What:="S", _
                            Replacement:="C", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
    Columns("C").Replace What:="s", _
                            Replacement:="C", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
End If

End Sub

 

[hsolanki]

Hi Sorry do i need to place the code into the Logdetails sheet or an new module?

 

[Fluff]

That depends on what you are trying to do. Where is the code at the moment?

 

[hsolanki]

i have got it on the logdetails sheet. i trying to do when the AR is found on column C then it is to change all the S & s to Completed

 

[Fluff]

Why are you using a Calculate event?