Set properties for multiple ranges

[Noz2k]

Just a quick question I think, I want to set the properties of certain cells in more than 1 range and was wondering if this is possible. To explain better here is a portion of my code

With ws2.Range("A18")
.Value = "Name"
.BorderAround ColorIndex:=0, Weight:=xlThin
.Interior.Color = RGB(204, 192, 218)
.Font.Bold = True
.Locked = False
End With

Now I also want to give A29, A40, A51, etc the same properties and was wondering if it were possible to include them in the same with statement, rathing than having to copy and paste the whole thing and just change the cell reference?

 

[JackDanIce]

Try:

With ws2.Range("A18, A29, A40, A51")
    .Value = "Name"
    .BorderAround ColorIndex:=0, Weight:=xlThin
    .Interior.Color = RGB(204, 192, 218)
    .Font.Bold = True
    .Locked = False
End With

 

[Noz2k]

Thought it would just be something simple,

Thank you, that saves me a lot of wasted typing time

 

[JackDanIce]

You're welcome

 

[Noz2k]

Okay, so I've now made things a little more complicated. Is it possible to do the same thing, but do so using an offset?

So instead of Range("B18, B29, B40, B51") I could say

Range("B18, B18.Offset(NoM, 0), B18.Offset(NoM * 2, 0), B18.Offset(NoM * 3, 0)

Where NoM is a variable

NoM = Sheets("MainMenu").cboName.ListCount +1

 

[Weaver]

I'd be tempted to use a loop thusly:

For i = 1 To 3
    With ws2.Range("B18").Offset(nom * i, 0)
        .Value = "Name"
        .BorderAround ColorIndex:=0, Weight:=xlThin
        .Interior.Color = RGB(204, 192, 218)
        .Font.Bold = True
        .Locked = False
    End With
Next i

 

[Noz2k]

Thank you, I think I should be able to use that method to fit exactly what I need.