Hi,

I am trying to write a macro do automatically format 6 cells in a row.
This I've managed, but find I'm stuck on the next bit. I want the
macro to repeat the format in the next row (and subsequent rows) until
the row has no data in it. The macro so far is

Range("F18:J18").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

End Sub

I want to repeat this until cell F in the next row is empty. I suspect
I need something like a "do until" loop, but can't see how to write it
as all the examples I've seen don't seem to cover formatting.

Can anyone please point me in the right direction to get this
achieved?
--
Cheers

Peter

Please remove the invalid to reply

Hi Peter,

One way to loop is the following

Dim i As Integer
Dim LastRow As Integer

LastRow = Cells(Cells.Rows.Count,"F").End(xlUp).Row

For i = 18 to LastRow
Range("F" & i &":J" & i).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Next i
End Sub


HTH
Cheers
Carim

On 31 Aug 2006 01:40:38 -0700, "Carim" wrote:

Hi Peter,

One way to loop is the following

Dim i As Integer
Dim LastRow As Integer

LastRow = Cells(Cells.Rows.Count,"F").End(xlUp).Row

For i = 18 to LastRow
Range("F" & i &":J" & i).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Next i
End Sub


HTH
Cheers
Carim

Hope this helps? Too right it does - it's does exactly what I want!

many thanks

--
Cheers

Peter

Please remove the invalid to reply

On 31 Aug 2006 01:40:38 -0700, "Carim" wrote:

Hi Peter,

One way to loop is the following

Dim i As Integer
Dim LastRow As Integer

LastRow = Cells(Cells.Rows.Count,"F").End(xlUp).Row

For i = 18 to LastRow
Range("F" & i &":J" & i).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Next i
End Sub


HTH
Cheers
Carim

Hi Carim,

As ever, one solution poses another question!

The macro works beautifully, but when you merge cells that have data
in them you get a warning message:

"The selection contains multiple data. Merging into one cell will keep
the upper-left most data only"

Is there a way to make the macro auto accept this warning for each
line?

--
Cheers

Peter

Please remove the invalid to reply

Hi Peter,

Merged cells are a nightmare and should be avoided as much as possible
....
they were created for purely "cosmetic reasons" ...

However, to bypass your problem, try using SendKeys to skip the warning
....

HTH
Cheers
Carim

On 31 Aug 2006 04:13:00 -0700, "Carim" wrote:

Hi Peter,

Merged cells are a nightmare and should be avoided as much as possible
...
they were created for purely "cosmetic reasons" ...

However, to bypass your problem, try using SendKeys to skip the warning
...

HTH
Cheers
Carim

Hi Carin,

Yes, I agree that merged cells are pain, I just whish I hadn't used
them when producing the spreadsheet (which needed to mimic a paper
form.

--
Cheers

Peter

Please remove the invalid to reply

Peter,

'application.displayalerts = false' will turn of all warnings.

Mike.

"Peter" wrote in message
...
On 31 Aug 2006 01:40:38 -0700, "Carim" wrote:

Hi Peter,

One way to loop is the following

Dim i As Integer
Dim LastRow As Integer

LastRow = Cells(Cells.Rows.Count,"F").End(xlUp).Row

For i = 18 to LastRow
Range("F" & i &":J" & i).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Next i
End Sub


HTH
Cheers
Carim

Hi Carim,

As ever, one solution poses another question!

The macro works beautifully, but when you merge cells that have data
in them you get a warning message:

"The selection contains multiple data. Merging into one cell will keep
the upper-left most data only"

Is there a way to make the macro auto accept this warning for each
line?

--
Cheers

Peter

Please remove the invalid to reply