You can use this code in private module of the sheet:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Range("C5").Value <> ActiveSheet.Name Then
ActiveSheet.Name = ActiveSheet.Range("C5").Value
End If
End Sub
Works only on Change event, means it does not run if a formula result changes, only after entering data in cells.
You can try changing to Worksheet_Activate
if you want to rename sheet based on this event...(will run when you open this sheet)