Private Sub CommandButton2_Click()
Dim c As Range
For Each c In Range("A1:A" & Range("A65536").End(xlUp).Row)
If c = [E4] Then
c.Interior.ColorIndex = 4
c.Offset(0, 1).Interior.ColorIndex = 45
c.Offset(0, 2).Interior.ColorIndex = 44
c.Offset(0, 2).Value = c.Offset(0, 1).Value
Else
c.Interior.ColorIndex = xlNone
c.Offset(0, 1).Interior.ColorIndex = xlNone
c.Offset(0, 2).Interior.ColorIndex = xlNone
c.Offset(0, 2).Value = ""
End If
Next c
End Sub