Allow enter value

Any manual entry in B1 will overwrite the formula in it. You need to have an alternative cell for manual input, e.g. D1

=IF(A1="NO",D1,SUM(C1:C5))

You could even ive an instruction to make the alternative entry in D1

=IF(A1="NO",SUM(C1:C5),IF(ISBLANK(D1),"Enter in D1",D1))

I want to look cell from end user if it no

Hi Jamal,

Please copy and paste this code on Worksheet, if user change the value on A1, macro will run automatically and apply the formula if A1 = No, else B2 will be blank user can update there own value

Also attached snap shot, Please make sure highlighted fields are correct selected

.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    If Range("A1").Value = "No" Then
    Range("B2").Value = "= sum(c1:c5)"
    Else
    Range("B2").Value = ""
    End If
End If
End Sub

Select allOpen in new window

I wouldn't recommend VBA for this with the problems of having to explain how to enable macros, etc. If you do use it the because A1 is actually the Target in this event then the code should refer to it as Target and the result may as well be automatically entered and UCase allows for NO.No or no to be used.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    If UCase(Target.Value) = "NO" Then
        Range("B2").Value = Application.WorksheetFunction.Sum(Range("C1:C5"))
    Else: Range("B2").Value = ""
    End If
End Sub

Select allOpen in new window

Thanx
But how lock cell from update if cell =sum

Are you talking with code? The worksheet would need to be protected

here's an example

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const PW As String = "Secret"
    If Target.Address <> "$A$1" Then Exit Sub
    Me.Unprotect PW
    If UCase(Target.Value) = "NO" Then
        With Range("B2")
            .Value = Application.WorksheetFunction.Sum(Range("C1:C5"))
            .Locked = True
        End With
        Me.Protect PW
    Else: Range("B2").Value = ""
    End If
End Sub

Select allOpen in new window

Lock-Cell.xlsm

It work but if A1 is No than i cant change it value or any other value in worksheet ... want to look only one cell

You need to set up sheet protection properly. The code only locks B2 in my example all other cells remain unlocked, so follow the instructions here:
Protect a worksheet

Thanx as I expected

Sorry I bothered trying to help, Obviously you don't appreciate correct code. There's no need to select cells, that is accepted by all efficient coders and what I said about using Target should be addressed plus you do not need ActiveSheet because the code will automatically refer to the sheet it is contained in, you can use the keyword  Me to refer to the sheet in this instance