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.
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.
You can also use a custom format of ;;; see this support article. This does the same thing and will still work if you add different colours for 4, 5, 6 …
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.
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.
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; I verified it works with Office 2013.
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.
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.
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!