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:
... I get a "Run-time error 1004: Selection is too large".
How can I work around this issue?
Thanks for any insights!