String script problem

[Beachcomber1224]

Hello,

I am having some trouble with the following script (below). In column B, I have a script that converts scores that I enter to a "refined" score, depending on the row (i.e. strSub1 for B5, B6, B8 or str3210 for B7, B11, B15). At the end of the script, I have it set up so that it adds these "scores". I need this to run for each column, but I seem to have made an error. I would greatly appreciate anyone's help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Const strSub1 As String = "B5,B6,B8,B9,B10,B12,B13,B14,B17,B18,B20,B22,B23,B24,B27,B28,B29,B31,B32,B33,B34,B35,B37,B38,B39,B40,B41,B43,B45,B46,B48,B50,B51,B53,B54,B55,B57,B58,B60,B61B62,B63,B64,B65,B66,B67,B68,B69"
    Const str3210 As String = "B7,B11,B15,B16,B19,B21,B25,B26,B30,B36,B42,B44,B47,B49,B52,B56,B59"
    
    Dim ColIndex As Long, rngSub1 As Range, rng3210 As Range
    For ColIndex = 2 To Me.Cells(4, Columns.Count).End(xlToLeft).Column
        
        Set rngSub1 = Me.Range(strSub1).Offset(0, ColIndex - 2)
        Set rng3210 = Me.Range(str3210).Offset(0, ColIndex - 2)
        
        Dim CellValue As Range
        For Each CellValue In rngSub1
            Select Case CellValue.Value
                Case 1 To 4
                    CellValue.Offset(67, 0).Value = CellValue.Value - 1
                Case Else
                    CellValue.Offset(67, 0).ClearContents
            End Select
        Next CellValue
        
        For Each CellValue In rng3210
            Select Case CellValue.Value
                Case 1 To 4
                    CellValue.Offset(67, 0).Value = 4 - CellValue.Value
                Case Else
                    CellValue.Offset(67, 0).ClearContents
            End Select
        Next CellValue
        
        Me.Cells(138, ColIndex).FormulaR1C1 = "=SUM(R[-66]C:R[-2]C)"
    
    Next
    
End Sub

When I try to run it, I am getting the following error message
"Run time error'1004':
Method "Range"of object_'Worksheet' failed

This is the part of the code that is giving me the problem:

 Set rngSub1 = Me.Range(strSub1).Offset(0, ColIndex - 2)

Does anyone have suggestions? Much appreciated!

 

[Ralajer]

The error is because a comma is missing between B61 and B62 in the Const strSub1 As String definition.

 

[Beachcomber1224]

Haha - thank you for catching my mistake. It works perfectly now!