Autonumber in Excel based on Criteria

[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

 

[SkipVought]

please post VBA code questions in Forum707.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]