I have one sheet, Tab 1, with a 38x12 grid of values ranging from -30 to +30. I applied the traditional 3-color scheme conditional formatting to the data, which shows all shades of the spectrum between the three colors (came out great...)

On Tab 2, I have a grid (38x12, same relative order of cells) with qualitative data in the cells. I want to apply the same color scheme to the grid on Tab 2 but keep the quantitative data only on the first tab.

I came up with a weaker solution where I use a formula in conditional formatting to link the grid in Tab 2 to Tab 1 and manually create 5 rules in which I choose a dark green, light green, yellow, light red, and dark red to apply based on the values of Tab 1. This does not have the same effect as the full spectrum though.

Basically, what I need is for excel to have a Paste Special -> Background color but unfortunately that's not yet an option.


1 Answer 1


No, it doesn't seem this is possible with VBA.

But, here's a ridiculous work-around.

Copy your cells from tab1 and paste them into microsoft word

Then copy that from word back to excel. Delete the values. Now you can format paint them to the other tab.

enter image description here

enter image description here

enter image description here

Now, format paint.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.