Excel 2007 Pasting Conditional Formats causes 'Selection too large' error

Jeffrey Smith
Jeffrey Smith used Ask the Experts™
on
Hi Experts,

I have an Excel 2007 xlsm file that's about 12 MB, 25,000 rows and 28 columns, 25 of which comprise the dataset.  I am trying to find a way to copy the Conditional Formats from Row 9 to the other 25,000 rows and having 2 problems.

1st, I can't seem to make the copy relative.  If I select the range A9:Y9 and specify Conditional Formatting formula criteria like:

="$Y9=""RUNWAY ACTIVE"""              (and Format the Fill color as Blue)
= "$Y9=""RUNWAY NOT ACTIVE"""     (and Format the Fill color as Red)

And then with the range A9:Y9 selected, use the Format Painter to paste the formats to (in the uploaded CFTest file which has only 30 records) the range B9:Y30, I then go to cell Y18 for example, and look at the CF rules, it is still referencing Row 9,in Row 18 like this:

="$Y9=""RUNWAY NOT ACTIVE"""

... even though I did not use an absolute row reference.

How do I make the Pasting of Conditional Format formula criteria relative?

2nd, When I try to use either the Format Painter as above or Copy, Paste Special | Formats in the original 25,000 row file, I get the error "Selection too large".  And if I try to use code like:

Sub PasteFormat()
[A9:Y9].Copy
[A10:Y25674].PasteSpecial Paste:=xlPasteFormats
End Sub

... I get a "Run-time error 1004: Selection is too large".

How can I work around this issue?

Thanks for any insights!

Jeff

 CFTest.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
1. I don't seem to be getting that error, so it may be relative to your pc setup.
2. Regarding relative referencing, they're there. Your formulas look good. On the edit area of conditional format, it's always going to show the formula in terms of the first cell in the range that that rule "applies to" (in your example it's y9) but on the subsequent rows, it really is looking at rows 10,11 etc. to evaluate the formula.
If you're not getting the result you thought you would, it's probably due to the order that the rules are being applied, or the stop if true option.
3. For applying it to 25k rows: Have you tried
-going into the edit criteria formula window,
-Change the dropdown from current selection to "This worksheet" (looking at the formats for the entire worksheet is good, so you can make sure there aren't any conflicting rules going on)
-Then, for each of the rules, change the 'applies to' range accordingly?
 $y9="Runway not active..." APPLIES TO:$a$9:$y$25000

Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

Interestingly enough, I just read one of the latest blog posts of the contextures.com blog, which covers exactly that issue. Maybe you can use it.


http://blog.contextures.com/archives/2010/10/18/excel-error-selection-is-too-large/

cheers, teylyn

Author

Commented:
Hi all & thanks for posting.

@ J79123:

> 1. I don't seem to be getting that error, so it may be relative to your pc setup.

The error only occurs with the original 12 MB 25k record file, not the 30 record example I uploaded.

> 2. Regarding relative referencing ...  
If you're not getting the result you thought you would, it's probably due to the order that the rules are being applied, or the stop if true option.

In the uploaded CFTest file, I went through the review you suggested in 3. above, made some changes, and I'm still not getting the results I expected.  I have uploaded screen shots of both the CF Dialog and the resulting application of the CF's.  As can be seen on Rows 18 and 25, the CF's did not display.

>3. For applying it to 25k rows: Have you tried
-going into the edit criteria formula window,
-Change the dropdown from current selection to "This worksheet" (looking at the formats for the entire worksheet is good, so you can make sure there aren't any conflicting rules going on)
-Then, for each of the rules, change the 'applies to' range accordingly?

I can't get it to work with the uploaded file yet ... if I can get past that, I hope to be able to apply it to the original 25K file ...

@ teylyn

I had come across that Contextures blog but couldn't figure out how to apply the code to my situation ... though I am willing to learn ;--).

Jeff
NorieAnalyst Assistant
Commented:
Jeff
You could avoid this by not using Selection and paste in the first place.
There is no need to set the conditional formatting for a few cells and then use the format painter to copy it to other cells.
You can either select the entire range and set the conditional formatting for all the cells, basing the reference in the formula
on the cell in the top left of the range.
Try something like this.
Obviously change the text to check for, the range to apply it to and the actual format.
PS You might want to clear existing conditional formatting.
One of the disadvantages of being able to apply more formats in Excel 2010 is that they kind of pile up and
become a bit 'fragmented.

Sub CreateFormat()
Dim rng As Range
Dim fm As FormatCondition

    Set rng = Range("A9:Y30")
    
    Set fm = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$Y9=""TRACK NOT ACTIVE""")
    With fm.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
   
End Sub

Open in new window

Author

Commented:
Sorry it has taken me so long to get back to this - this is primarily due to ongoing computer issues I'm having, including the inability to use an Advanced Filter on this file (actually, a "parent" file of this same 12 MB file.  While both my problems (the CF issue here and this other Advanced Filter issue which I also opened a separate EE question for) have now "gone away", I don't really know what the problems (or the solutions) were ... can only surmise some kind of corruption in the file that resolved when I rebuilt the file.  Under the circumstances, my selection of a "solution" will be largely arbitrary, but I do want to award points to reflect my appreciation for your help.  I'm awarding most of the points to J79123, because his/her comment: "On the edit area of conditional format, it's always going to show the formula in terms of the first cell in the range that that rule "applies to" (in your example it's y9) but on the subsequent rows, it really is looking at rows 10,11 etc. to evaluate the formula" served to help me make sense of what the resulting (subsequent) CF's should look like.  

@ imnorie: I think my problems lay elsewhere (And re-building the file is seemingly what it took to fix it), but I appreciated your code solution, too, and would have turned to that if the re-build hadn't helped.

Thanks to all.

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial