Multiple Column Reference

[lee2smooth03]

Hello everyone,

I need some help referencing columns in a range. Below is a sample of the code. In the code, the first column of the range is given a numerical format and some color; likewise, the last column is given a different color and format. My goal is to target the cells in the middle columns.

Function formatData(theData As Range)
'this function colors the first and last columns of the range
'it also sets the numerical format for the dates as well as centers all of the numbers

With theData.Columns(1)                     'targets the first column
    .Interior.Color = RGB(83, 141, 213)
    .NumberFormat = "mm/dd/yyyy"
End With


With theData.Columns(theData.Columns.Count)  'targets the last column
    .Interior.Color = RGB(255, 255, 102)
    .NumberFormat = "00"
End With

End Function

I want the remaining center columns to have similar formats, but I do not know how to address all of them at once using the "with" statement. Here's what I had in mind:

• With theData.Columns(2:theData.Columns.Count - 1)
• With theData.Columns(2 to theData.Columns.Count -1)

Does anyone have any suggestions?

P.S.
Does anyone know how to center-align the cells using with?

 

[Jonmo1]

Try

With Range(theData.Columns(2), theData.Columns(theData.Columns.Count-1))