Assigning serial numbers to a specific range in the first column

Question

I'm trying to add a vba section to my project which assigns serial numbers to the coloured range of the first column, however without success. I've come up with the follwing code:

Private Sub CommandButton1_Click()

    On Error GoTo ErrorHandler
    
    Dim serial, i, EndRow, StartRow As Integer
    Dim row As Range, cell As Range
    
    'Discover the data starting and end rows
    i = 1
    serial = 1
    StartRow = 1
    EndRow = 1

    'Check the first cell of each row for the data-start background colour
    For Each row In ActiveSheet.UsedRange.Rows
        Cells(row.row, 1).Select
        If i < 3 Then
            If Hex(cell.Interior.Color) = "47AD70" And i = 1 Then
                Cells(row.row, 1).Value = Abs(serial)
                StartRow = serial
                serial = serial + 1
                i = 2
            ElseIf Hex(cell.Interior.Color) = "47AD70" And iRow = 2 Then
                Cells(row.row, 1).Value = Abs(serial)
                serial = serial + 1
            ElseIf Hex(cell.Interior.Color) <> "47AD70" And iRow = 2 Then
                EndRow = serial - 1
                i = 3
            End If
        End If
    Next row
    
ErrorHandler:
    If Err.Number <> 0 Then
        Msg = "Error # " & Str(Err.Number) & " was generated by " _
         & Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
        MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    End If
End Sub

The variable i is used as a flag to detect the coloured range.

By the end, the green cells A5 to A22 should be filled with numbers 1 to 18. Also variable StartRow should end up being assigned value = 5 (starting row of the coloured range), and EndRow should end up being assigned value = 22 (Ending row of the coloured range.

My code generates Error# 91, Object variable or With block variable not set.

Apart from the error which I'm not able to rectify, I know the code itself is not that clever either and there may be more efficient code to achieving the objective.

Could someone please suggest a resolution or even a better code? Many thanks

Answer 1

I may not fully understand what you are trying to do. The code below will look for the first green cell in column A, give that cell the serial number 1, and continue numbering subsequent green cells serially. I hope that is what you would like.

Private Sub CommandButton1_Click()

    Const Col       As Long = 4697456           ' = &H47AD70
    
    Dim Ws          As Worksheet
    Dim Rcount      As Long                     ' Row
    Dim R           As Long
    
    Set Ws = ActiveSheet                        ' better = Worksheets("Sheet1")
    With Ws
        Rcount = .UsedRange.Rows.Count
        For R = 2 To Rcount
            ' find the frist occurrence of Col
            If .Cells(R, "A").Interior.Color = Col Then Exit For
        Next R
        
        If R > Rcount Then
            MsgBox "No cells of the specified colour were found.", _
                   vbInformation, "Unuccessful search"
        Else
            Rcount = R - 1
            Do
                .Cells(R, "A").Value = R - Rcount
                R = R + 1
                ' loop until a different cell colour is encountered
            Loop While .Cells(R, "A").Interior.Color = Col
        End If
    End With
End Sub

Note that it's usually dangerous to make changes on the ActiveSheet. The danger is in the user's erroneously running the code while the wrong sheet is active. Therefore it's better to modify the above code to specify the intended sheet by name.

Answer 2

1. Please, replace

Cells(row.row, 1).Select

with

Set cell = cells(row.row, 1)
cell.Select 'not necessary, but you maybe want seeing what happens during testing...

Only selecting a cell, does not tell to VBA that this is the cell the code needs. :)

2. You have a typo:

And iRow = 2 Then

It, obviously should be:

And i = 2 Then

3. Which is the purpose of using EndRow variable. If it not used in order to do something. It only receives a value at the end...

4. On Error GoTo ErrorHandler can be used/has a meaning only if you put On Error Resume Next in the first code lines. Otherwise, no any error will be caught.

Then, it is not recommended to use variable names like row, cell. They may create problems in debugging of a complex code...