How do I copy conditional formatting only?

[Alan Olrog]

Hi. I'm using Excel 2002 SP3.

Can I copy just the Conditional Formatting of a cell without copying other
formatting?
Failing that, can I copy the whole formatting of a cell to the rest of a
filtered column without affecting the hidden cells?

I have 400+ rows, columns A-Z, and each row has one of five styles applied
which sets the font colour.
Based on the text string in column AB I want to apply conditional formatting
to columns A-Z of the row which will set just the shading of the cell.
I can successfully set up the conditional formatting (CF); the problem is in
propagating it to other rows.

For example, if I start with cell A1 and CF 'Formula Is' =($AB1="Not yet in
use") that works fine.
I'd then like to copy the CF down column A and automatically change the
formula to $AB2, $AB3 etc. However if I use Paste Special to 'Fill Formatting
Only' it also changes the style of each cell to A1's style.

How can I easily copy the CF down Column A without altering other formatting?

As an alternative method, I can apply an Autofilter to select rows of one
style, but Paste Special then applies formatting to the hidden cells which is
no help. Strangely, using plain handle-drag Autofill on an Autofiltered
column only affects the visible rows, but of course, this copies values too.

Any suggestions appreciated. I'm happy to give VBA a try.

 

[Leo Rod]

Edit - Copy ->then Edit - Paste Special - Formats

This will copy all formats, including conditionals, then you can reformat
any cell borders-colors or others that you did not wanted to be copied, if
any.

Let me know how it worked.

Leo Rod
Mia, Fl, USA

 

[Alan Olrog]

Thanks for your reply Leo.
I know that I can copy formats the way you describe - I was trying to avoid
having to re-format the 400+ rows afterwards, which would involve re-applying
styles and borders.

I was hoping for a way of copying just the conditional formatting. Or, if I
Autofilter first by Style, to copy formatting to the filtered (visible) rows
without applying it to the hidden rows.
I seem to remember seeing something about the 'visible' rows when using
filters, but can't recall the details.
Thanks anyway. Your method may well be the quickest in the end.
Alan Olrog

 

[Alan Olrog]

Thanks for your reply Leo.
I know that I can copy conditional formats using the method you describe but
I wanted to avoid having to re-apply formats (styles and borders) afterwards
to the 400+ rows.

Alternatively, if I Autofilter first, I then want to copy formats to the
visible rows without affecting the hidden rows.
I seem to remember something about 'visible' rows when using filters but I
can't recall the details.

Your method may well turn out to be the quickest in the end.
Alan Olrog

 

[Alan Olrog]

DUPLICATE : (I re-typed my reply after MS said the first one failed)

 

[wal]

Here's how to copy-paste conditional formatting ONLY:

Select a cell that has the conditional formatting set already. Open
the conditional formatting dialog. Under "Applies to", either type in
the cell addresses (as a range, e.g., $A$5:$G$5) to which you want the
conditional formatting to apply OR click on the red/blue icon and
select the range manually in the spreadsheet. Click OK.

The above works for Excel 2007. As I recall, it worked for 2003 as
well.

Hope this helps. (Don't you wish the Help files would tell you this?
Maybe they do somewhere, but if you type in a search term like "Paste
conditional formatting", you get 100 results none of which are on
point, or if they are, the title of the Help page doesn't clue you in
on which one to choose.)