Hi all,

I would like to format a sheet with many rows (10000) by VBA. Each row
has a record type stored in one of the columns. For each record type,
there is a template row. All rows of a kind shall then be formated like
this template row.

Therefore I'm using "Pastespecial Paste:=xlPasteFormats". That works
fine, but it's too slow (2 or three minutes for 4000 rows, but I have
even more rows). Is there a better & faster way? I tried styles as
well, but the colums of the rows are formatted using many different
colors, borders, number formats etc. so that won't work.

Here's the code as it is now:
For i = 20 To 30000
Select Case Range("M" & i).Value ' this contains the record
type
Case "a"
Rows(1).Copy
Rows(i).PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Case "b"
Rows(2).Copy
Rows(i).PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.... and so on ....
Else
Exit For
End If
Next i

TIA
gary