Auto generate serial number in excel based on Column

• (Manual run code) Yeah, it wasn't suppossed to run automatically. Place it in a standard module (Hit Alt+F11, goto top menu, Insert, Module, paste code in big white field). Hit Alt+F8 to open available macro's and double click RunMe, to see result.
• (Auto run code) To run automatically, paste the following code in the big white field after right-clicking the sheets tab and clicking View Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub
Dim x, lrow As Integer
lrow = Range("D1").End(xlDown).Row
Range("A2:A" & lrow).ClearContents
x = 1
For Each cell In Range("D1:D" & lrow - 1)
    If cell.Value <> cell.Offset(1, 0).Value Then
        cell.Offset(1, -3).Value = x
        x = x + 1
    End If
Next cell
End Sub

• (Formula reverse result) I tried to use standard functions, but all I could get was reverse result, so a 1 at the bottom and then increment to the top. Formula in cell A2: =IF(D1<>D2,COUNT(A3:A11)+1,"") and drag it down as far as needed.
• (Custom function) If you really want to use a formula, I created a custom one for you. Place the code below in a standard module (just like mentioned "Manual run code" option):

Function SNo(dCell As Range)
If dCell.Value <> dCell.Offset(-1, 0).Value Then
    If dCell.Offset(-1, -3).Value = vbNullString Then
        SNo = Application.WorksheetFunction.Count(Range("A2:A" & dCell.Row - 1)) + 1
    ElseIf Application.WorksheetFunction.IsText(dCell.Offset(-1, -3).Value) = True Then
        SNo = 1
    ElseIf Application.WorksheetFunction.IsNumber(dCell.Offset(-1, -3).Value) = True Then
        SNo = dCell.Offset(-1, -3).Value + 1
    End If
Else
    SNo = vbNullString
End If
End Function