A way to establish DEFAULT BACKGROUND COLOR?

[evanowar]

Hello,


I'm new to this blog and new-ish to Excel, so hopefully you more savvy users can offer some help. I have a worksheet that I've divided into three "background" colors, if you will. (I call these background colors, but I created this formatting with just the normal Fill function.) Then "on top" of these background colors I often highlight some cells with additional colors. My hope is to be able to copy/cut-and-paste between the three regions while retaining only the highlight colors. I do not want to transfer the background color, and I'm indifferent to all other formatting. (It's identical between the three regions.) Is there a simple fix for this? Perhaps a way to define a true default background color, not just use Fill as I did? I searched online with no success. As it stands now, when I paste an area containing both the background color and highlighted cells, I have a lot of tedious tidying up afterwards. If I use Paste Values, the pasted cells adopt the new background color, but don't retain the highlights. If I use the default Paste function, I have to manually modify the background color after pasting. Lastly, when I CUT-and-paste (rather than copy), I have to fill in the white gap left in the wake of the cut cells.

I will have to use this file very frequently, so any help is much appreciated. I have a rudimentary understanding of VBA, and would gladly implement VBA code that anybody suggests. I considered a For Each command that would assess the color of each cell in a copied range, and then paste either without formatting (if the cell's color is one of the three background colors) or with formatting (if the cell's color is not a background color). I don't know if this is really feasible, but before I even attempted to code, it occurred to me that when pasting, the current Selection is not the copied cells, but rather the target cells. Trying to work around that would be out of my skillset.


Thanks in advance!


-E

 

[SirJB7]

Hi, evanowar!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Never heard about anything that let you do in one step. You could try these workarounds:

a) A macro that set backgrounds properly.

b) If no other backgrounds are set, then conditional formatting by range with a rule like this for each group would do the job:

=AND(ROW()>=<col.i.from>,ROW()<<col.i+1.to&#62

where <col.i.from> and <col.i+1.from> are the 1st and 2nd first columns of the ranges of each color.


Regards!