I'm looking for a way to apply the formatting from one cell (i.e. range object) to another cell without using:

sourceRange.Copy

targetRange.PasteSpecial <options>

Application.CutCopyMode = False

The implicit selection of the targetRange is slowing down my macro, and making the sheet scroll unnecessarily.

Note:

I've already included the hack Application.ScreenUpdating = False, but this seems less than elegant, IMHO.

rusty

per our support engineer:

I worked on the issue and got a sample code, on my machine it is working fine. There is no flickering or unnecessary scrolling of screen. Please pass this code to ISV and check if it solves his problem.

Dim MyRange As String

Range("A1:c100").Select ' Select the source range only once

Selection.Copy ' Copy the source range

MyRange = "E1:g10000" 'Set the Range

'Use the paste special option on the range on which you want to apply the formatting

Range(MyRange).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False 'Make the Application CutCopyMode False

Use this code if you need multiple ranges to be copied.

Dim MyRange As String

Range("A1").Select ' Select the source range only once

Selection.Copy ' Copy the source range

For I = 1 To 10000 'Execute a Loop if different range needs to be selected

MyRange = "E" + Str(I)

'Use the paste special option on the variety of ranges on which you want to apply the formatting

Range(MyRange).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

Next

Application.CutCopyMode = False 'Make the Application CutCopyMode False

End Sub

-brenda (ISV Buddy Team)

Actually what I have is a sheet with 8 cells that are used as format "templates". The macro in question moves down the cells in a given column and applies the formatting of one of the template cells based on the contents of the current cell.

The template cells are named ranges, and the macro initializes 8 corresponding range objects as the named ranges.

I was hoping there was some kind of method of the range object that would duplicate the formatting of another range object, as in:

<targetrange>.CopyFormatting(<sourcerange>)

or something similar.

This doesn't seem to exist, so I will stick with the <sourcerange>.Copy, <targetrange>.PasteSpecial process I'm using now.

rusty