SOLVED
Home

Extracting the color from a conditionally formatted cell and applying the color to another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-303501%22%20slang%3D%22en-US%22%3EExtracting%20the%20color%20from%20a%20conditionally%20formatted%20cell%20and%20applying%20the%20color%20to%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303501%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20tables%20with%20numeric%20values%2C%20Origin%20and%20Destination.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20I%20want%20to%20conditionally%20format%20the%20Origin%20table%20on%20a%20blue%2Fred%20spectrum%20with%200%20as%20the%20midpoint%2C%20which%20is%20the%20easy%20part.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20hard%20part%3A%20applying%20the%20colors%20from%20the%20Origin%20table%20to%20the%20Destination.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20using%20%22Format%20Painter%22%20but%20that%20applies%20the%20conditional%20formatting%20rule%20to%20the%20Destination%20table%2C%20which%20changes%20the%20colors.%26nbsp%3B%20I've%20also%20tried%20creating%20a%20custom%20conditional%20formatting%20rule%20and%20using%20the%20Formula%20option--this%20also%20doesn't%20work%20as%20you%20cant%20get%20the%20desired%20color.%26nbsp%3B%20Copy%2FPaste%20Special%20doesn't%20extract%20the%20conditional%20colors%20either.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20the%20most%20likely%20solution%20(if%20there%20is%20even%20one)%20is%20to%20use%20some%20vba.%26nbsp%3B%20I've%20tried%20using%20the%20Interior.Color%20method%20with%20mixed%20success.%26nbsp%3B%20Additionally%2C%20I'm%20not%20sure%20excel%20recognizes%20a%20conditionally%20formatted%20color%20as%20the%20Interior.Color.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20file%20attached%20with%20the%20tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-303501%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-303667%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20the%20color%20from%20a%20conditionally%20formatted%20cell%20and%20applying%20the%20color%20to%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-303667%22%20slang%3D%22en-US%22%3E%3CP%3EI%20didn't%20play%20with%20this%2C%20check%26nbsp%3B%3CFONT%3E%3CA%20href%3D%22https%3A%2F%2Fyoursumbuddy.com%2Fconditional-formatting-color-scales-based-on-other-cells%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fyoursumbuddy.com%2Fconditional-formatting-color-scales-based-on-other-cells%2F%3C%2FA%3E%3C%2FFONT%3E%20if%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

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.

1 Reply
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies