Successive retrieval of defined ranges by splitting thier names in a constant as well as a dynamic part

Question

The parking management tool should contain the code below. Essentially, it is only conditional formatting of parking spots in the form of defined ranges in a sheet called "GF" based on an associated status that is defined as a string in a list object in a sheet called "GF List."

I want to address the entire problem using a VBA method rather than the typical conditional formatting because, unlike the example below, the code will be applied later to several hundred parking spaces with eight potential formattings.

Because I can't format the "CurrentLot" as a Range using a dynamically-retrieved value, the code fails.

Sub No_01_to_05a()

Dim gfList As Worksheet
Dim gfPlan As Worksheet
Dim status As String
Dim CurrentLot As Range
Dim i As Integer
Dim No As String

Set gfList = ThisWorkbook.Worksheets("GF List")
Set gfPlan = ThisWorkbook.Worksheets("GF")


'Parking lots that are defined manually here
Dim LotNo1 As Range
Set LotNo1 = gfPlan.Range("B2", "C2")

Dim LotNo2 As Range
Set LotNo2 = gfPlan.Range("D2", "E2")

Dim LotNo3 As Range
Set LotNo3 = gfPlan.Range("F2", "G2")

Dim LotNo4 As Range
Set LotNo4 = gfPlan.Range("H2", "I2")

Dim LotNo5 As Range
Set LotNo5 = gfPlan.Range("J2", "K2")

Dim LotNo5a As Range
Set LotNo5a = gfPlan.Range("M2", "M3")

'ForNext procedure
For i = 4 To gfList.Range("E" & Application.Rows.Count).End(xlUp).Row
    
    status = gfList.Range("E" & i).Value
    No = gfList.Range("B" & i).Value
    CurrentLot = "LotNo" & No 'Line that does not seem to work
    
        If status = "Vacant" Then
                CurrentLot.Interior.Color = RGB(255, 255, 0)
                CurrentLot.Font.Color = RGB(0, 0, 0)
    
        ElseIf status = "Let" Then
                CurrentLot.Interior.Color = RGB(146, 208, 80)
                CurrentLot.Font.Color = RGB(0, 0, 0)

        ElseIf status = "Reserved" Then
                CurrentLot.Interior.Color = RGB(0, 176, 240)
                CurrentLot.Font.Color = RGB(0, 0, 0)
                
        Else
                CurrentLot.Interior.Color = RGB(255, 255, 255)
                CurrentLot.Font.Color = RGB(0, 0, 0)     
        End If
                  
Next i

End Sub

Answer 1

You can use a Select Case statement :

Option Explicit

Sub No_01_to_05a()

    Dim gfList As Worksheet
    Set gfList = ThisWorkbook.Worksheets("GF List")
    Dim status As String
    Dim CurrentLot As Range
    Dim i As Integer
    Dim No As String

    'ForNext procedure
    For i = 4 To gfList.Range("E" & Application.Rows.Count).End(xlUp).Row
    
        status = gfList.Range("E" & i).Value
        No = gfList.Range("B" & i).Value
        'CurrentLot = "LotNo" & No   <= You cannot assign a string to a range
        Set CurrentLot = getLot(No)
    
        If status = "Vacant" Then
            CurrentLot.Interior.Color = RGB(255, 255, 0)
            CurrentLot.Font.Color = RGB(0, 0, 0)
    
        ElseIf status = "Let" Then
            CurrentLot.Interior.Color = RGB(146, 208, 80)
            CurrentLot.Font.Color = RGB(0, 0, 0)

        ElseIf status = "Reserved" Then
            CurrentLot.Interior.Color = RGB(0, 176, 240)
            CurrentLot.Font.Color = RGB(0, 0, 0)
                
        Else
            CurrentLot.Interior.Color = RGB(255, 255, 255)
            CurrentLot.Font.Color = RGB(0, 0, 0)
        End If
                  
    Next i

End Sub

Function getLot(ByVal No As Long) As Range
    
    Dim gfPlan As Worksheet
    Set gfPlan = ThisWorkbook.Worksheets("GF")
    
    'Parking lots that are defined manually here
    Dim LotNo1 As Range
    Set LotNo1 = gfPlan.Range("B2", "C2")

    Dim LotNo2 As Range
    Set LotNo2 = gfPlan.Range("D2", "E2")

    Dim LotNo3 As Range
    Set LotNo3 = gfPlan.Range("F2", "G2")

    Dim LotNo4 As Range
    Set LotNo4 = gfPlan.Range("H2", "I2")

    Dim LotNo5 As Range
    Set LotNo5 = gfPlan.Range("J2", "K2")

    Dim LotNo5a As Range
    Set LotNo5a = gfPlan.Range("M2", "M3")
    Select Case No
        Case 1
            Set getLot = LotNo1
        Case 2
            Set getLot = LotNo2
        Case 3
            Set getLot = LotNo3
        Case 4
            Set getLot = LotNo4
        Case 5
            Set getLot = LotNo5
        Case Else
            '
    End Select
End Function