As a total noob, working from MS Access VBA,
I find myself doing a lot of this:

-------------------------------------------
2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
..Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

2040 With .Borders(xlLeft)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlRight)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlTop)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlBottom)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2049 End With
2999 End With
------------------------------------------

I tried .BordersAround, but Excel didn't buy it.

Is there something else?
--
PeteCresswell

Pete, A couple of ways:

With Range("A1")
With .Borders
.Weight = xlMedium
End With
End With

Range("D1").BorderAround xlSolid, xlMedium

NickHK

"(PeteCresswell)" wrote in message
...
As a total noob, working from MS Access VBA,
I find myself doing a lot of this:

-------------------------------------------
2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
.Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

2040 With .Borders(xlLeft)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlRight)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlTop)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlBottom)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2049 End With
2999 End With
------------------------------------------

I tried .BordersAround, but Excel didn't buy it.

Is there something else?
--
PeteCresswell

You could put the border contstants in an array then loop through the array.
The for loop would go inside of your With statement. Example below. Or, you
might write a separate sub and pass arguments to it for LineStyle and Weight
and the border constants (passing an array for the borders since they could
vary or maybe use a parameter array).


Dim arrBorders as variant
Dim i as long

arrBorders = Array(xlLeft, xlRight, xlTop xlBottom)

2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
..Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

for i = lbound(arrborders) to ubound(arrborders)
With .Borders(arrBorders(i))
.Weight = xlMedium
.LineStyle = xlSolid
End With
next i


2049 End With
2999 End With






"(PeteCresswell)" wrote:

As a total noob, working from MS Access VBA,
I find myself doing a lot of this:

-------------------------------------------
2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
..Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

2040 With .Borders(xlLeft)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlRight)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlTop)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlBottom)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2049 End With
2999 End With
------------------------------------------

I tried .BordersAround, but Excel didn't buy it.

Is there something else?
--
PeteCresswell

I tried .BordersAround, but Excel didn't buy it.

It wouldn't, it's a typo, see below

Sub test()

With ActiveSheet
With .Range(.Cells(3, 2), .Cells(3, 6))
With .Cells(1, 1)
.Value = "my text or value"
.HorizontalAlignment = xlCenter
'gExcelColor_MediumBlue, guessing -
'will match to nearest colour in the palette
.Interior.Color = RGB(153, 204, 255)
' or asumming a default palette
'.Interior.ColorIndex = 37
End With
.Merge
.BorderAround xlSolid, xlMedium
' or if using late binding
'.BorderAround 1, -4138
End With
End With
End Sub

Regards,
Peter T

"(PeteCresswell)" wrote in message
...
As a total noob, working from MS Access VBA,
I find myself doing a lot of this:

-------------------------------------------
2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
.Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

2040 With .Borders(xlLeft)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlRight)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlTop)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlBottom)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2049 End With
2999 End With
------------------------------------------

I tried .BordersAround, but Excel didn't buy it.

Is there something else?
--
PeteCresswell

Just to add, probably better to use constants like xlEdgeLeft rather than
xlLeft.

If looping the outside borders could do it this way

With rng
For i = 7 To 10
.Borders(i).Weight = xlMedium
.Borders(i).LineStyle = xlSolid
Next
End With

To include inside borders, whether or not there are any, simply apply to
..Borders in one go.

..Borders.Weight = xlMedium
..Borders.LineStyle = xlSolid

Regards,
Peter T

"JMB" wrote in message
...
You could put the border contstants in an array then loop through the
array.
The for loop would go inside of your With statement. Example below. Or,
you
might write a separate sub and pass arguments to it for LineStyle and
Weight
and the border constants (passing an array for the borders since they
could
vary or maybe use a parameter array).


Dim arrBorders as variant
Dim i as long

arrBorders = Array(xlLeft, xlRight, xlTop xlBottom)

2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
..Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

for i = lbound(arrborders) to ubound(arrborders)
With .Borders(arrBorders(i))
.Weight = xlMedium
.LineStyle = xlSolid
End With
next i


2049 End With
2999 End With






"(PeteCresswell)" wrote:

As a total noob, working from MS Access VBA,
I find myself doing a lot of this:

-------------------------------------------
2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
..Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

2040 With .Borders(xlLeft)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlRight)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlTop)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlBottom)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2049 End With
2999 End With
------------------------------------------

I tried .BordersAround, but Excel didn't buy it.

Is there something else?
--
PeteCresswell