Autonumber in Excel based on Criteria 1

[tcolan]

Hi,

I'm looking to autonumber in Column A based on criteria in Column B. In other words, once someonse selects "Open" in Column B, I want the Column A cell to add the next unique number in the range of Column A. I want to use VBA since I want this value to be static. Here's what I have and I'm coming to understand that the Max() function isn't going to help me with this one. Any assistance would be appreciated.

Thanks
Tom

Dim rng3 As Range
Dim r3 As Range
Set rng3 = Application.Intersect(Target, Range("B:B"))
If Not rng3 Is Nothing Then

Application.EnableEvents = False

For Each r3 In rng3
With r3
If Cells(.Row, "B").Value = "Open" Then
Cells(.Row, "A").Value = Max("A:A") + 1
End If
End With
Next r3

Application.EnableEvents = True

End If

 

[AnotherHiggins]

Put this in the Sheet (as opposed to a module):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        If Target.Text = "Open" Then
            Target.Offset(, -1) = Application.WorksheetFunction.Max(Range("A:A")) + 1
        Else
            Target.Offset(, -1) = Null
        End If
    End If
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[tcolan]

Works great!

Thanks!
Tom