Excel VBA Object variable not set

Question

I'm trying to learn VBA while creating a useful spreadsheet.

I'm working with a provided spreadsheet that has cells that have been filled with vbRed.  I want this program to step to each of these cells and wait for a typed entry which would be a string like "SBB130".

I execute this program by selecting to run the EnterRooms() subrouting.

I keep getting this error message:

"Object variable or With block variable not set"

I believe it occurs at the line

        sheet.UsedRange.Select

in the following "program".

All help greatly appreciated!

Function SelectColoredCells()

    Dim sheet As Worksheet

    Dim rng As Range

    Dim rCell As Range

    Dim lColor As Long

    Dim rColored As Range

        Set sheet = ActiveSheet

        sheet.UsedRange.Select

    lColor = vbRed

    Set rColored = Nothing

    For Each rCell In Selection

        If rCell.Interior.Color = lColor Then

            If rColored Is Nothing Then

                Set rColored = rCell

            Else

                Set rColored = Union(rColored, rCell)

            End If

        End If

    Next

    SelectColoredCells = rColored

End Function

Sub EnterRooms()

Dim Rooms As Range

Dim Cell As Range

Dim x As Variant

    Rooms = SelectColoredCells()

    For Each Cell In Rooms

        x = InputBox("Enter Room ID")

    Next Cell

    MsgBox "Finished"

End Sub

Answer 1

Replace your code with the following. Set was not used to assign ranges to range variables. See bolded text. Defining the function As Range is not essential but it is good programming

Code edited since initial posting to include entering the number in the red cells. Using Cell as a variable is not good because it can be confused with reserved words. I have changed it to rngCell.

Function SelectColoredCells() As Range

     Dim sheet As Worksheet

     Dim rng As Range

     Dim rCell As Range

     Dim lColor As Long

     Dim rColored As Range

         Set sheet = ActiveSheet

         sheet.UsedRange.Select

     lColor = vbRed

     Set rColored = Nothing

     For Each rCell In Selection

         If rCell.Interior.Color = lColor Then

             If rColored Is Nothing Then

                 Set rColored = rCell

             Else

                 Set rColored = Union(rColored, rCell)

             End If

         End If

     Next

     Set SelectColoredCells = rColored

 End Function

 Sub EnterRooms()

 Dim Rooms As Range

 Dim rngCell As Range

 Dim x As Variant

     Set Rooms = SelectColoredCells()

     For Each rngCell In Rooms

         x = InputBox("Enter Room ID")

         rngCell.Value = x    'If the number is to be entered in the red cells

     Next rngCell

     MsgBox "Finished"

 End Sub

Answer 2

OssieMac,

Thanks! Also for the quick response!

Your fixes made it work.  Now I have to improve it to make it user friendly.

Best wishes,

GSObob

Answer 3

Note that I edited the code after my initial posting. This post because otherwise you do not get an email advice that the post has been edited.