Auto Format of completed row

Question

This code should change all the text in a row to green if there is a value entered into column AD. However, this is not occurring automatically. I should add that when the macro is run manually, the "completed" rows text does change to the correct color. This code snippet was added into the Microsoft Excel Objects Sheet1. Another thing that occurs is the header row text gets colored when the macro is manually run. How do I stop the header row text from getting colored and have this macro run automatically?

Private Sub Worksheet_Calculate()
    RowComplete
End Sub

Sub RowComplete()
    Dim row As Range
    For Each row In ActiveSheet.UsedRange.Rows
        If row.Cells(1, "AD").Value > 0 Then
            row.Font.Color = RGB(0, 176, 80)
        Else
            row.Font.ColorIndex = xlNone
        End If
    Next row
End Sub

Answer 1

Is this what you want?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Set r = Target.EntireRow

If Target.row = 1 Then Exit Sub 'don't change header color

If r.Cells(1, "D").Value <> "" Then 'change "D" to applicable column
    r.Font.Color = RGB(0, 176, 80)
Else
    r.Font.ColorIndex = xlNone
End If

End Sub