CODEWITHSUNDEEP
vbaexcel
billypilgrim
billypilgrim

Reputation: 145

Selecting Cells when looping through Columns in VBA

I have a macro that formats a multi-sheet excel document. On each sheet, there is a table of data, and I am trying to color code the columns based on what the header row (row 1) says.

I would like to select the 2nd cell in the column, up to the last row of data, and set the background color. I currently have something that looks like this:

For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Activate

        ' Convert to table
        If .UsedRange.ListObject Is Nothing Then
            .ListObjects.Add SourceType:=xlSrcRange,
                             Source:=.UsedRange, 
                             xllistobjecthasHeaders:=xlYes, 
                             TableStyleName:="Table Style 1"
        End If

        ' Additional Formatting
        ...

        ' Adjust columns
        For Each col In .UsedRange.Columns
            Header = col.Cells(1, 1)

            ' Additional formatting that works
            ...

            Dim col_color As Variant

            ' Color Code
            If Header = "A" Then
                col_color = xlThemeColorAccent6 ' Orange
            ElseIf Header ="B" Then
                col_color = xlThemeColorLight2 ' Blue
            ElseIf Header = "C" Then
                col_color = xlThemeColorAccent4 ' Purple
            ElseIf Header ="D" Then
                col_color = xlThemeColorAccent3 ' Green
            ElseIf Header = "E" Then
                col_color = xlThemeColorAccent2 ' Red
            End If

            Range(col.Cells(2, 1)).Select ' ERROR HERE
            Range(Selection, Selection.End(xlDown)).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = col_color
                .TintAndShade = 0.8
                .PatternTintAndShade = 0
            End With


        Next col

I am getting the following error on Range(col.Cells(2, 1)).Select, "Method 'Range' of object '_Global' failed".

My question is how would I properly select the 2nd cell in the current column iteration of the loop?

Upvotes: 2

Views: 793

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I would like to select the 2nd cell in the column, up to the last row of data, and set the background color.

There is no need to make a Selection to set the background color. Declare a range variable properly and use that instead:

Dim formatRange as Range
Set formatRange = Range(col.Cells(2, 1), col.Cells(2, 1).End(xlDown) )

With formatRange.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = col_color
    .TintAndShade = 0.8
    .PatternTintAndShade = 0
End With

Your error occurs because you've only passed a single argument to the Range method, which is a cell, the default property of which is it's Value, so unless col.Cells(2,1) contains as its value a valid range address string, this will always fail. You could've avoided it by passing two parameters to Range in a single Select:

Range(col.Cells(2, 1), col.Cells(2, 1).End(xlDown)).Select
With Selection.Interior
    ...

Or:

 col.Cells(2, 1).Select
 Range(Selection, Selection.End(xlDown)).Select
 With Selection.Interior
    ...

Or even:

 col(2,1).Select
 Range(Selection, Selection.End(xlDown)).Select
 With Selection.Interior
    ...

But you should not do those things:

It is nearly universally better to avoid Activate/Select.

Upvotes: 3

Related Questions