Conditional Formatting

[JAC]

I have correctly applied conditional formatting to a number of cells
on a worksheet belonging to a workbook, involving named ranges and
formulae. All is well.

However, when I copy a range of cells (30 cells) from the worksheet to
another workbook using VBA using the following type of construct, I do
not get the result I desire.

' Paste the values, retaining the original formatting, etc

With rngDest
.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

The conditions for the conditional formats are carried over, including
the formulae and such named ranges that are essential to satisfy the
conditions.

However, all I intended to do was to carry over the resulting format
that should be applied to the pasted cells, not the conditions or the
formulae.

Does anyone know how I can do this? Can it be done by applying
different parameters to the PasteSpecial method?

Or, will I have to set the formatting myself on the worksheet after
pasting, taking note of the result of the conditional formatting?

Many thanks

 

[JAC]

I have correctly applied conditional formatting to a number of cells
on a worksheet belonging to a workbook, involving named ranges and
formulae. All is well.

However, when I copy a range of cells (30 cells) from the worksheet to
another workbook using VBA using the following type of construct, I do
not get the result I desire.

        ' Paste the values, retaining the original formatting, etc

        With rngDest
            .PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
            .PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With

The conditions for the conditional formats are carried over, including
the formulae and such named ranges that are essential to satisfy the
conditions.

However, all I intended to do was to carry over the resulting format
that should be applied to the pasted cells, not the conditions or the
formulae.

Does anyone know how I can do this? Can it be done by applying
different parameters to the PasteSpecial method?

Or, will I have to set the formatting myself on the worksheet after
pasting, taking note of the result of the conditional formatting?

Many thanks

I have discovered a workaround using a bit of lateral thinking, which
I enclose here just in case anyone else encounters a similar problem
and is desperate for a solution.

1. The trick is to place the conditional formulae (each yielding a
boolean T/F result) in unused cells on the source worksheet. If
necessary, conceal the results using white text on a white background.
2. On the cells that you want to apply conditional formatting, set the
FormulaIs box to point to the cell containing the appropriate
condition in step 1.
3. Then, when you come to copy and paste the relevant cells to a new
workbook by value with formats etc, the correct formatting will be
applied, without transcription of defined names, etc. which may be
meaningless in the new workbook. Don't forget to include the cells
used in step 1.

This works quite nicely.

If anyone has a better suggestion, I would be glad to see it.

Thanks.

JAC