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

 

[Fluff]

Does that mean col C has a formula or not?

 

[hsolanki]

hi No Column C has no formulas

 

[Fluff]

Is that code in the "LogDetails" code module, or some other module?

 

[hsolanki]

Hi the code is in the "LogDetails" sheet

 

[Fluff]

In that case I see no reason why it wouldn't work & it works for me.
Are you sure that code is actually running?

 

[hsolanki]

Hi Yes the code runs however nothing happens as all the S remains the same it does not changes to C when AR is found

 

[Fluff]

How do you know that the code runs, if nothing changes?

 

[hsolanki]

i tried doing an debug the code and had a msgbox if AR is found. also when code is finishes the S does not change to C on the sheet

 

[hsolanki]

is there any alternative way to achieve the same result. when ar is found on column C all the capital S and lower S changes to C??

 

[Fluff]

Your code works for me as long as it's in the "LogDetails" code module, but will only run when a formula on that sheet is calculated.