Windows – Copy Conditional Formatting 3-Color Scheme to another tab

conditional formattingmicrosoft excelmicrosoft-excel-2007windows

I have one sheet, Tab 1, with a 38×12 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 (38×12, 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.

Best Answer

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.

Now, format paint.

Related Solutions

Excel 2007 Conditional Formatting is not properly using custom formula provided

Your problem is INDIRECT. It is not playing nicely with your conditional formulas, which seems to be some sort of limitation around INDIRECT.

However, I don't think you need it. If I understand your requirement correctly, you can just change the green conditional formula to =IF((MOD(ROW(),2) = 1),ISNUMBER($E1), FALSE). The use of $E1 will force the formula to reevaluate for each row, so it turns into:

=IF((MOD(ROW(),2) = 1), ISNUMBER($E1), FALSE) for E1
=IF((MOD(ROW(),2) = 1), ISNUMBER($E2), FALSE) for E2
=IF((MOD(ROW(),2) = 1), ISNUMBER($E3), FALSE) for R3
etc.

Similarly, you can replace your red formula with =IF(MOD(ROW(),2) = 1,NOT(ISNUMBER("$E1)), FALSE)

Excel 2007: Conditional formatting so that each row shows low values yellow, high values red

Here's a macro that creates a conditional format for each row in your selection. It does this by copying the format of the first row to EACH row in the selection (one by one, not altogether). Replace B1:P1 with the reference to the first row in your data table.

Sub NewCF()
    Range("B1:P1").Copy
    For Each r In Selection.Rows
        r.PasteSpecial (xlPasteFormats)
    Next r
    Application.CutCopyMode = False
End Sub

To use, highlight the un-formatted rows in your dataset (in my case, B2:P300) and then run the macro. In the example below, note that the max numbers in the first two rows are 5 and 15, respectively; both cells are dark red.

I'm sure there's a faster solution than this, though.

enter image description here

Best Answer

Related Solutions

Excel 2007 Conditional Formatting is not properly using custom formula provided

Excel 2007: Conditional formatting so that each row shows low values yellow, high values red

Related Question