I have two tables with numeric values, Origin and Destination.
First, I want to conditionally format the Origin table on a blue/red spectrum with 0 as the midpoint, which is the easy part.
The hard part: applying the colors from the Origin table to the Destination.
I've tried using "Format Painter" but that applies the conditional formatting rule to the Destination table, which changes the colors. I've also tried creating a custom conditional formatting rule and using the Formula option--this also doesn't work as you cant get the desired color. Copy/Paste Special doesn't extract the conditional colors either.
I believe the most likely solution (if there is even one) is to use some vba. I've tried using the Interior.Color method with mixed success. Additionally, I'm not sure excel recognizes a conditionally formatted color as the Interior.Color.
Please see the file attached with the tables.
