automatically change all cell values based on updated entry

Right click the sheet tab and select vie code and copy and paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
Dim nEndRw As Long, vColB As Variant, i As Long

nEndRw = Cells(Rows.Count, "B").End(xlUp).Row
vColB = Target.Offset(0, -1).Value
Application.EnableEvents = False

For i = 1 To nEndRw
    If Cells(i, "B").Value <> "" Then
        If Cells(i, "B").Value = vColB Then
            Cells(i, "C").Value = Target.Value
        End If
    End If
Next i

Application.EnableEvents = True

End Sub

thank you so much Mr. Sixthsense, it works like a charm. one more thing, how to make it works for all sheets at once??
Thank you again, you're the man.

Glad it helps you and thanks for the feedback

Just a first line has been changed in the below code, DELETE the previous code from the sheet event code. The below code should be pasted in THISWORKBOOK code window. Refer the below to know how to add the code in THISWORKBOOK CODE window.

To add code in ThisWorkbook Code Window

1. Copy the below code
2. Select the workbook in which you want to store the code
3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
4. In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
5. Right-click on the ThisWorkbook object, and choose View Code
6. Where the cursor is flashing, choose Edit | Paste

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub
Dim nEndRw As Long, vColB As Variant, i As Long

nEndRw = Cells(Rows.Count, "B").End(xlUp).Row
vColB = Target.Offset(0, -1).Value
Application.EnableEvents = False

For i = 1 To nEndRw
    If Cells(i, "B").Value <> "" Then
        If Cells(i, "B").Value = vColB Then
            Cells(i, "C").Value = Target.Value
        End If
    End If
Next i

Application.EnableEvents = True

End Sub

Originally Posted by ruliansyah

what if it run automatically without having to change worksheet??

Not sure what you are trying to say by way of the above sentence

Works Great, you're the man, you save me thank you so so so much for your help.

another question,
Still the same topic but to change the value on column B itself, let say I have some B cell with the value "Test A" then if I change the value in any cell from "Test A" to "Test A1" it would change all "Test A" value on entire B column into "Test A1" and so within another sheet. I believe it use oldvalue method but I don't know how it works.

Glad it works and thanks for the feedback

Replace the previous code of THISWORKBOOK code with the below one

Dim vPreValue As Variant

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vPreValue = Target.Value
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
Dim Ws As Worksheet, nEndRw As Long, vColB As Variant, i As Long, sCol As String

Application.EnableEvents = False

For Each Ws In ThisWorkbook.Sheets
    With Ws
        nEndRw = .Cells(Rows.Count, "B").End(xlUp).Row
        If Target.Column = 2 Then
            sCol = "B"
            vColB = vPreValue
        Else
            vColB = Target.Offset(0, -1).Value
            sCol = "C"
        End If
        
        For i = 1 To nEndRw
            If .Cells(i, "B").Value <> "" Then
                If .Cells(i, "B").Value = vColB Then
                    .Cells(i, sCol).Value = Target.Value
                End If
            End If
        Next i
    End With
Next Ws

Application.EnableEvents = True

End Sub

Sorry I missed to read the FORMULA Issue, Replace the previous code with the below Revised Code

Dim vPreValue As Variant

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
vPreValue = Target.Value

End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
Dim Ws As Worksheet, nEndRw As Long, vColB As Variant, i As Long

Application.EnableEvents = False

For Each Ws In ThisWorkbook.Sheets
    With Ws
        nEndRw = .Cells(Rows.Count, "B").End(xlUp).Row
        
        If Target.Column = 2 Then
            vColB = vPreValue
        Else
            vColB = Target.Offset(0, -1).Value
        End If
        
        For i = 1 To nEndRw
            If .Cells(i, "B").Value <> "" Then
                If .Cells(i, "B").Value = vColB Then
                    .Cells(i, Target.Column) = Target
                End If
            End If
        Next i
    End With
Next Ws

Application.EnableEvents = True

End Sub

I believe this will be the Final Code, Replace the previous code with the below:-

Dim vPreValue As Variant

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
vPreValue = Target.Value

End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("B:C")) Is Nothing Then Exit Sub
Dim Ws As Worksheet, nEndRw As Long, vColB As Variant, i As Long

On Error GoTo ErrorOccurred
Application.EnableEvents = False
Application.ScreenUpdating = False

For Each Ws In ThisWorkbook.Sheets
    With Ws
        nEndRw = .Cells(Rows.Count, "B").End(xlUp).Row
        
        If Target.Column = 2 Then
            vColB = vPreValue
        Else
            vColB = Target.Offset(0, -1).Value
        End If
        
        For i = 1 To nEndRw
            If .Cells(i, "B").Value <> "" Then
                If .Cells(i, "B").Value = vColB Then
                    .Cells(i, Target.Column) = Target.Formula
                End If
            End If
        Next i
    End With
Next Ws

Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub

ErrorOccurred:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    MsgBox "An Error Occurred", vbCritical, "Error Alert"
    
End Sub

Glad it fulfilled your requirement completely

Once again thanks for the feedback