trying to optimize repetitive Coding

[parodytx]

Excel 2010, VBA, Windows 7. intermediate VBA experience.

I have a large spreadsheet that generates other spreadsheets from downloaded data.
I am trying to optimize the coding so as not to keep repeating the same formatting code for multiple columns, for example.

Kindly responses to prior posts have shown how to do this with hard range references, but now I am stuck with logical references in a series.

I have tried to get the same formatting applied to the following series of ranges without success:

For Each myRange In Range("(Cells(6, 12), Cells(JRow, 12)), (Cells(6, 24), Cells(JRow, 24)), _
                          (Cells(6, 28), Cells(JRow, 28)), (Cells(6, 38), Cells(JRow, 38)), _
                          (Cells(6, 41), Cells(JRow, 41)), (Cells(6, 46), Cells(JRow, 46)), _
                          (Cells(6, 50), Cells(JRow, 50)), (Cells(6, 53), Cells(JRow, 53)), _
                          (Cells(6, 57), Cells(JRow, 57))") as Range
    With myRange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
Next myRange

I have tried with no quotes, quotes around each range, and as above and it just won't compile. There may be ways to do it without a loop, but I am not that sophisticated in VBA yet.

Any help or suggestions would be much appreciated.

 

[timfritsch]

This may help - the first answer seems promising: Excel VBA: Populate array with range from specific sheet - Stack Overflow

Tim

 

[MARK858]

to try and get your code working try the code below. Change jrow to suit

Dim jrow As Long, myRange As Range
jrow = 1
For Each myRange In Union(Cells(6, 12), Cells(jrow, 12), Cells(6, 24), Cells(jrow, 24), _
                          Cells(6, 28), Cells(jrow, 28), Cells(6, 38), Cells(jrow, 38), _
                          Cells(6, 41), Cells(jrow, 41), Cells(6, 46), Cells(jrow, 46), _
                          Cells(6, 50), Cells(jrow, 50), Cells(6, 53), Cells(jrow, 53), _
                          Cells(6, 57), Cells(jrow, 57))
    With myRange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
Next

 

[parodytx]

to try and get your code working try the code below. Change jrow to suit

Dim jrow As Long, myRange As Range
jrow = 1
For Each myRange In Union(Cells(6, 12), Cells(jrow, 12), Cells(6, 24), Cells(jrow, 24), _
                          Cells(6, 28), Cells(jrow, 28), Cells(6, 38), Cells(jrow, 38), _
                          Cells(6, 41), Cells(jrow, 41), Cells(6, 46), Cells(jrow, 46), _
                          Cells(6, 50), Cells(jrow, 50), Cells(6, 53), Cells(jrow, 53), _
                          Cells(6, 57), Cells(jrow, 57))
    With myRange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
Next

Sorry, that gives a compile error at the second comma.

I had an idea about using an array to specify the column variables. That worked.

Dim myArr as Variant
Dim x as Variant

myArr = Array(12, 24, 28, 32, 41, 46, 50, 53, 57)                        
For Each x In myArr
Range(Cells(6, x), Cells(JRow, x)).Select
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
End With
Next x

This now iteratively formats all the columns referenced in the array list. No reason it should not work for other functions as well.

Thanks for your help.

 

[MARK858]

Sorry, that gives a compile error at the second comma.

Did you copy and paste the code or re-type it because I get no compile error and I have copied and pasted the code back from the forum into my VBE and ran the code again?

 

[mikerickson]

With Application.Intersect(Range("BE1,BA1,ao1,at1,ab1,af1,L1,Z1").EntireColumn, Application.Union(Rows(6), Rows(jRow)))
    With .Interior
         .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
        .Interior.ColorIndex = 3
    End With
End With

 

[MARK858]

Hi Mike I don't think the Interior in

[COLOR="#FF0000"].Interior[/COLOR].ColorIndex = 3

should be there