Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lastRow As Long
If Target.Address = "$E$2" Then
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("B2:B" & lastRow).Replace What:=Range("E1"), Replacement:=Range("E2"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Range("E1").Value = Range("E2").Value
End If
End Sub