Using Names Range in Excel VBS not working in some circumstances

Question

I'm trying to use a named range to control cell formats, but it does not appear to work as expected:

The Sub that does not use the named range, works fine:

Sub COLOR_PATCH()
'
' This changes the interior color of a block of cells to whatever hex value the current active cell contains
'
Dim XX As String 'XX IS THE HEX VALUE of an RGB color
If ActiveCell.Value <> "" Then
    XX = ActiveCell.Value
    Range("$L$8:$R$31").Interior.Pattern = xlSolid
    Range("$L$8:$R$31").Interior.PatternColorIndex = xlAutomatic
    Range("$L$8:$R$31").Value = XX
    Range("$L$8:$R$31").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    Range("$L$8:$R$31").Interior.Color = HexToRGB(XX) 'HexToRGB is a function to convert HEX to RGB
    Range("$L$8:$R$31").Interior.TintAndShade = 0
    Range("$L$8:$R$31").Interior.PatternTintAndShade = 0
End If
End Sub

When I created named range, I got a Run-time error '1004' Method 'Range' of Object '_Global' failed

Sub COLOR_PATCH()
Dim CBLOCK As Range
Dim XX As String
Set CBLOCK = Range("$L$8:$R$31")
If ActiveCell.Value <> "" Then
    XX = ActiveCell.Value
    Range(CBLOCK).Interior.Pattern = xlSolid
    Range(CBLOCK).Interior.PatternColorIndex = xlAutomatic
    Range(CBLOCK).Value = XX
    Range(CBLOCK).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    Range(CBLOCK).Interior.Color = HexToRGB(XX)
    Range(CBLOCK).Interior.TintAndShade = 0
    Range(CBLOCK).Interior.PatternTintAndShade = 0
End If
End Sub

Answer 1

• CBLOCK is a Range object. The code should be

CBLOCK.Interior.Pattern = xlSolid

• Select is not necessary in your code.

How to avoid using Select in Excel VBA

Sub COLOR_PATCH()
    Dim CBLOCK As Range
    Dim XX As String
    Set CBLOCK = Range("$L$8:$R$31")
    XX = ActiveCell.Value
    If XX <> "" Then
        With CBLOCK
            .Interior.Pattern = xlSolid
            .Interior.PatternColorIndex = xlAutomatic
            .Value = XX
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Interior.Color = HexToRGB(XX)
            .Interior.TintAndShade = 0
            .Interior.PatternTintAndShade = 0
        End With
    End If
End Sub