Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy conditional formatting only?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy conditional formatting only?
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy conditional formatting only?
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 "Leo Rod" wrote: 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy conditional formatting only?
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 "Leo Rod" wrote: 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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy conditional formatting only?
DUPLICATE : (I re-typed my reply after MS said the first one failed)
"Alan Olrog" wrote: 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 "Leo Rod" wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy conditional formatting only?
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.) On Aug 26, 9:02*pm, Alan Olrog wrote: Thanks for your reply Leo. I know that I cancopyconditionalformats 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 tocopyformats 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 "Leo Rod" wrote: Edit -Copy-then *Edit - Paste Special - Formats This willcopyall 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" wrote in message ... Hi. I'm using Excel 2002 SP3. Can Icopyjust theConditionalFormattingof a cell without copying other formatting? Failing that, can Icopythe wholeformattingof 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 applyconditional formatting to columns A-Z of the row which will set just the shading of the cell.. I can successfully set up theconditionalformatting(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 tocopythe 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 easilycopythe 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 appliesformattingto 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.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy conditional formatting | New Users to Excel | |||
Can I copy conditional formatting? | New Users to Excel | |||
... Can I Copy Conditional Formatting... | Excel Discussion (Misc queries) | |||
Copy Conditional Formatting | Excel Discussion (Misc queries) | |||
How to copy conditional formatting | Excel Worksheet Functions |