- (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
Then make a change in column D to see result.
- (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
SNo = vbNullString
Then type in cell A2:
and drag it down as far as needed.
Now there MUST be something to your liking :).