Find and replace with criteria - VBA code

If there are no blank rows in the data you could use

dim i as integer
i = 0
do while Range("C1").offset(i,0).value <> ""
If Range("C1").offset(i,0).Value > 5 Then

    Range("B1").offset(i,0).Select
    Selection.Replace What:="SERIES", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Else
        MsgBox ("NO DATA FOUND")
 End If
i = i + 1
loop

If there are blank rows you could use something like

dim i, r as integer
r = range("c"&rows.count).end(xlup).row
for i = 0 to r
If Range("C1").offset(i,0).Value > 5 Then

    Range("B1").offset(i,0).Select
    Selection.Replace What:="SERIES", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Else
        MsgBox ("NO DATA FOUND")
 End If
next

edit: Second one is tested. You will get a message box for ever row that does not have data so you may want to take that out.