Formatting cells within VBA
 

As you've all been so helpful in the last couple of days and I'm on a roll
this evening (I'm in the UK)...

I've recorded a macro to format lines on sheets the way I want them, but
it's producing big, ugly lumps of code. Is there any way of combining or
simplifying the repeated format instructions?

Eg. the following is taken from a set of formats that simply put borders
around each specified cell in a line:

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

This is repeated for each side,the internal divider and the horizontals!
surely there is a way of applying the same format conditions to multiple
"targets" (or whatever they are really called)

TIA (again!)

Dave

Formatting cells within VBA
 

Perhaps this...

Selection.Borders.LineStyle = xlContinuous

--
HTH...

Jim Thomlinson


"Risky Dave" wrote:

As you've all been so helpful in the last couple of days and I'm on a roll
this evening (I'm in the UK)...

I've recorded a macro to format lines on sheets the way I want them, but
it's producing big, ugly lumps of code. Is there any way of combining or
simplifying the repeated format instructions?

Eg. the following is taken from a set of formats that simply put borders
around each specified cell in a line:

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

This is repeated for each side,the internal divider and the horizontals!
surely there is a way of applying the same format conditions to multiple
"targets" (or whatever they are really called)

TIA (again!)

Dave

Formatting cells within VBA
 

Jim,

Again my thanks. I've just been able to delet about 100 lines of unused code
as a result of that!

Dave

"Jim Thomlinson" wrote:

Perhaps this...

Selection.Borders.LineStyle = xlContinuous

--
HTH...

Jim Thomlinson


"Risky Dave" wrote:

As you've all been so helpful in the last couple of days and I'm on a roll
this evening (I'm in the UK)...

I've recorded a macro to format lines on sheets the way I want them, but
it's producing big, ugly lumps of code. Is there any way of combining or
simplifying the repeated format instructions?

Eg. the following is taken from a set of formats that simply put borders
around each specified cell in a line:

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

This is repeated for each side,the internal divider and the horizontals!
surely there is a way of applying the same format conditions to multiple
"targets" (or whatever they are really called)

TIA (again!)

Dave

Formatting cells within VBA
 

That line weirds out if you have diagonals in your cells but otherwise it is
a time saver...
--
HTH...

Jim Thomlinson


"Risky Dave" wrote:

Jim,

Again my thanks. I've just been able to delet about 100 lines of unused code
as a result of that!

Dave

"Jim Thomlinson" wrote:

Perhaps this...

Selection.Borders.LineStyle = xlContinuous

--
HTH...

Jim Thomlinson


"Risky Dave" wrote:

As you've all been so helpful in the last couple of days and I'm on a roll
this evening (I'm in the UK)...

I've recorded a macro to format lines on sheets the way I want them, but
it's producing big, ugly lumps of code. Is there any way of combining or
simplifying the repeated format instructions?

Eg. the following is taken from a set of formats that simply put borders
around each specified cell in a line:

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

This is repeated for each side,the internal divider and the horizontals!
surely there is a way of applying the same format conditions to multiple
"targets" (or whatever they are really called)

TIA (again!)

Dave