0

Excel 2010:

This question relates specifically to the use of PasteSpecial with Conditional Formatting.

Overview: I have a template row stored in a different sheet. This row contains roughly 350 columns and numerous formats, formulas and conditional formatting.

Data is loaded from SQL Server and contains N rows - generally between 400 and 15000.

The doing:

I copy the template row and insert require number rows:

AryVariant = rst_ADODB.GetRows
lRecordCount = UBound(AryVariant, 2) + 1

Templates.Range("TEMPLATE_ROW").Copy
sheet1.Range(sheet1.Range("Range_Start").Offset(1, 0), sheet1.Range("Range_Start").Offset(lRecordCount, 0)).EntireRow.Insert Shift:=xlDown

Copying from another sheet like this into a range makes a mess of the formulas, and conditional formatting, but copies the cell formats fine so i then go back over the range to update the formulas and conditional formatting:

Templates.Range("TEMPLATE_ROW").Copy
sheet1.Range(sheet1.Range("Range_Start").Offset(1, 0), sheet1.Range("Range_Start").Offset(lRecordCount, 0)).EntireRow.PasteSpecial xlPasteFormats

The issue:

While this works for formulas, it merges the rows in conditional formatting in the "Applies To" part while leaving the Formula in the rule referring to the top row in the range. Example (not sure how to show this part as i can't upload images):

Formula: "=$FB18"

Format: Some format here

Applies To: "=$D$18:$M$11436,$O$18:$AC$11436"

The desired result:

What i want is every row to be self contained, so:

Formula: "=$FB18"

Format: Some format here

Applies To: "=$D$18:$M$18,$O$18:$AC$18"

Formula: "=$FB19"

Format: Some format here

Applies To: "=$D$19:$M$19,$O$19:$AC$19"

The work around:

Currently I am applying the PasteSpecial row by row as a workaround:

For r = 0 To UBound(AryVariant, 2)
    Templates.Range("TEMPLATE_ROW").Copy
    sheet1.Range("sheet1_Dataset").Offset(r + 1, 0).EntireRow.PasteSpecial xlPasteFormats
Next r

While this works, it is appallingly slow with large data-sets.

Finally, the question:

Is there a way to get the desired result while applying the formats to the range as a whole?

Pawel Czyz
  • 1,651
  • 4
  • 17
  • 21
G.Williams
  • 63
  • 7
  • You can apply conditional formatting to the entire range at once (either manually or with VBA). [this](https://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code?rq=1) may be of use – cybernetic.nomad May 28 '19 at 21:38
  • Greetings,There are many hundreds of cells per row with several dozen different conditional formats. The purpose of saving the template (which was created by a data model developer - not me) is to avoid hard coding hundreds of individual conditional formats. The issue is when copying and pasting the conditional format to a range, not creating it in the first place. Thanks though. – G.Williams May 28 '19 at 22:42

0 Answers0