# Copying only conditional formatting in Excel **URL:** \ **Category:** Factual Questions\ **Created:** [November 20, 2019, 4:54pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713 "2019-11-20T16:54:53Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Johnny\_Bravo](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_bravo/32/493_2.png) [@Johnny\_Bravo](https://boards.straightdope.com/u/Johnny_Bravo)\ **Post date:** [November 20, 2019, 4:54pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/1 "2019-11-20T16:54:53Z")
Here’s a super specific question about Excel! I have a spreadsheet where I keep track of some stuff using only three data points - call them 1, 2, and 3. I have conditional formatting set up so that the cells with a 1 are highlighted yellow, the cells with a 2 are red, and the cells with a 3 are grey. Because the cells are highlighted via conditional formatting, the highlighting clears as soon as the values are removed. This is Working as Intended. But I would like a spreadsheet that only has the conditional formatting - no values, just a bunch of blank cells highlighted in three colors. Can this be done? ---
**Author:** ![Dickerman](https://avatars.discourse-cdn.com/v4/letter/d/8baadc/32.png) [@Dickerman](https://boards.straightdope.com/u/Dickerman)\ **Post date:** [November 20, 2019, 5:01pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/2 "2019-11-20T17:01:28Z")
NM (misread question) ---
**Author:** ![Johnny\_Bravo](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_bravo/32/493_2.png) [@Johnny\_Bravo](https://boards.straightdope.com/u/Johnny_Bravo)\ **Post date:** [November 20, 2019, 5:04pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/3 "2019-11-20T17:04:59Z")
Right, but that just pastes the conditional rules - leaving me with a bunch of blank cells ready to be highlighted when I add the 1, 2, or 3 values. ---
**Author:** ![Dave.B](https://avatars.discourse-cdn.com/v4/letter/d/9f8e36/32.png) [@Dave.B](https://boards.straightdope.com/u/Dave.B)\ **Post date:** [November 20, 2019, 5:22pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/4 "2019-11-20T17:22:04Z")
Change the conditional format so the text is the same colour as the background? Not quite what you are after, but it will look like it. ---
**Author:** ![Borborygmi](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/borborygmi/32/217_2.png) [@Borborygmi](https://boards.straightdope.com/u/Borborygmi)\ **Post date:** [November 20, 2019, 5:34pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/5 "2019-11-20T17:34:17Z")
If there is a ‘correct’ way to do this, I don’t know what it is… but a simple workaround would be to keep the existing worksheet as it is, and then create a new worksheet that uses the same conditional formatting rules but based on the values in the original worksheet rather than the new worksheet. ---
**Author:** ![The\_Lurker\_Above](https://avatars.discourse-cdn.com/v4/letter/t/dc4da7/32.png) [@The\_Lurker\_Above](https://boards.straightdope.com/u/The_Lurker_Above)\ **Post date:** [November 20, 2019, 6:26pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/6 "2019-11-20T18:26:41Z")
> [@Dave.B](#): > > Change the conditional format so the text is the same colour as the background? Not quite what you are after, but it will look like it. You can also use a custom format of ;;; [see this support article](https://support.office.com/en-us/article/hide-or-display-cell-values-c94b3493-7762-4a53-8461-fb5cd9f05c33). This does the same thing and will still work if you add different colours for 4, 5, 6 … ---
**Author:** ![Johnny\_Bravo](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_bravo/32/493_2.png) [@Johnny\_Bravo](https://boards.straightdope.com/u/Johnny_Bravo)\ **Post date:** [November 20, 2019, 8:40pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/7 "2019-11-20T20:40:24Z")
> [@Dave.B](#): > > Change the conditional format so the text is the same colour as the background? Not quite what you are after, but it will look like it. :smack: That’s perfect. ---
**Author:** ![Vesicant](https://avatars.discourse-cdn.com/v4/letter/v/43a26b/32.png) [@Vesicant](https://boards.straightdope.com/u/Vesicant)\ **Post date:** [November 20, 2019, 11:54pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/8 "2019-11-20T23:54:19Z")
Just add a rule to your existing conditional formatting to format the cells when they’re blank. ---
**Author:** ![GreysonCarlisle](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@GreysonCarlisle](https://boards.straightdope.com/u/GreysonCarlisle)\ **Post date:** [November 21, 2019, 12:16am UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/9 "2019-11-21T00:16:55Z")
Are you adverse to VBA? Have a key sequence set up (CTRL+ALT+K or whatever) that will create a new worksheet from the background colors of the original sheet. When you’re done, just delete the new sheet. ---
**Author:** ![GreysonCarlisle](https://avatars.discourse-cdn.com/v4/letter/g/a87d85/32.png) [@GreysonCarlisle](https://boards.straightdope.com/u/GreysonCarlisle)\ **Post date:** [November 21, 2019, 12:26am UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/10 "2019-11-21T00:26:35Z")
> [@GreysonCarlisle](#): > > Are you adverse to VBA? Razzafraggin’ autocorrect. ---
**Author:** ![Vesicant](https://avatars.discourse-cdn.com/v4/letter/v/43a26b/32.png) [@Vesicant](https://boards.straightdope.com/u/Vesicant)\ **Post date:** [November 21, 2019, 1:13am UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/11 "2019-11-21T01:13:02Z")
I read the OP to say he or she wants to keep the conditional formatting rules and the corresponding cell colors, but have no data in the cells. Simply copying the background colors using VBA won’t do that. I suppose you could write the VBA code to also copy the conditional formatting rules, but that’s essentially the same as just copying the worksheet. And then when the data is deleted, there won’t be any formatting. If I’m misunderstanding the OP, please ignore. ---
**Author:** ![Kimble](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kimble/32/19185_2.png) [@Kimble](https://boards.straightdope.com/u/Kimble)\ **Post date:** [November 21, 2019, 6:29am UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/12 "2019-11-21T06:29:06Z")
One thing you might try is copying the table, paste it into _Word_, copy it in Word, and paste it back into Excel. I found that answer at [Stack Overflow](https://stackoverflow.com/questions/18548156/how-to-copy-the-conditional-formatting-without-copying-the-rules-from-a-conditio); I verified it works with Office 2013. ---
**Author:** ![lingyi](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lingyi](https://boards.straightdope.com/u/lingyi)\ **Post date:** [November 21, 2019, 7:45am UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/13 "2019-11-21T07:45:47Z")
You can add additional rules to Conditional Formatting. Manage Rules\>New Rule In addition to the rule that sets the cell(s) color, add a rule with a formula that sets additional criteria. To always keep the cell color, even when a value (number or letter) is entered, you could use _=$A$1:$A$25\>-1_ if your value will be a positive number or letter or you could change the negative value to something like -999999999 which is outside the range of negative values that may be entered. You could also use _=AND($A$1:$A$25\<\>15681689)_ where the \<\> value is a random number that won’t occur in the cell. I’m sure there are more elegant ways to use a formula, just a couple of options. ---
**Author:** ![lingyi](https://avatars.discourse-cdn.com/v4/letter/l/d2c977/32.png) [@lingyi](https://boards.straightdope.com/u/lingyi)\ **Post date:** [November 21, 2019, 7:58am UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/14 "2019-11-21T07:58:13Z")
> [@Vesicant](#): > > I read the OP to say he or she wants to keep the conditional formatting rules and the corresponding cell colors, but have no data in the cells. Simply copying the background colors using VBA won’t do that. I suppose you could write the VBA code to also copy the conditional formatting rules, but that’s essentially the same as just copying the worksheet. And then when the data is deleted, there won’t be any formatting. If I’m misunderstanding the OP, please ignore. Under Manage Rules of Conditional Formatting there’s a Stop if True checkbox Reread the OP’s question and Vesicant’s Post #8 is correct. Set Conditional Formatting to set color if the cells are blank. I thought the OP wanted to always keep the cells the same colors even if there were values. ---
**Author:** ![Johnny\_Bravo](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_bravo/32/493_2.png) [@Johnny\_Bravo](https://boards.straightdope.com/u/Johnny_Bravo)\ **Post date:** [November 21, 2019, 2:40pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/15 "2019-11-21T14:40:05Z")
edit: I asked another question and then figured out an answer two seconds later . Also, Dante B’s answer above will get me what I want. I know it isn’t the elegant solution but it fits perfectly into my very limited Excel skillset. Thanks everyone! ---
**Author:** ![zimaane](https://avatars.discourse-cdn.com/v4/letter/z/a3d4f5/32.png) [@zimaane](https://boards.straightdope.com/u/zimaane)\ **Post date:** [November 21, 2019, 3:06pm UTC](https://boards.straightdope.com/t/copying-only-conditional-formatting-in-excel/843713/16 "2019-11-21T15:06:36Z")
You can 1. Highlight the existing cells with the formatting that you desire 2. Copy 3. Open a new, blank Excel file 4. Choose Paste, Formatting (Alt H - V - R) The cells will now be blank and have the conditional formatting