SOLVED

Conditions formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2013015%22%20slang%3D%22en-US%22%3EConditions%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013015%22%20slang%3D%22en-US%22%3EI%20have%20two%20tables%2C%20and%20I%20want%20to%20compare%20between%20them%2C%20and%20in%20table%203%20the%20results%20of%20compared%20show%20in%20colors%20how%20I%20can%20do%20this%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2013015%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013066%22%20slang%3D%22en-US%22%3ERe%3A%20Conditions%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F910515%22%20target%3D%22_blank%22%3E%40Isra_sidki%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20provide%20more%20explanation%20to%20the%20conditional%20format%20you%20are%20trying%20to%20setup%3F%20Is%20it%20the%20condition%20supposed%20to%20be%20less%20than%20a%20certain%20column%20or%20flag%20a%20certain%20text%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20please%20provide%20a%20sample%20workbook%20of%20how%20your%20data%20is%20structured.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013220%22%20slang%3D%22en-US%22%3ERe%3A%20Conditions%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013220%22%20slang%3D%22en-US%22%3EThis%20is%20the%20attachment%20I%20want%20the%20table%20of%20tna%20look%20just%20in%20color%20without%20any%20text.%3CBR%20%2F%3EThx%20for%20the%20responding%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013286%22%20slang%3D%22en-US%22%3ERe%3A%20Conditions%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013286%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F910515%22%20target%3D%22_blank%22%3E%40Isra_sidki%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20below%20how%20one%20of%20the%20TNAs%20are%20populated%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E1.%20Assign%20values%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20included%20a%20grading%20system%20(1-3)%20to%20make%20it%20easier%20for%20the%20TNA%20column%20to%20read%20the%20match%20requirements.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E2.%20TNA%20Lookup%20Formula%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(OR(G14%3D%22%22%2CH14%3D%22%22)%2C%22%22%2CIF(G14%3DH14%2C%22MATCH%22%2C%0AIF(VLOOKUP(G14%2CSheet1!%24B%243%3A%24C%245%2C2%2CFALSE)%3CVLOOKUP%3E%3C%2FVLOOKUP%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20returns%20either%20MATCH%2C%20BELOW%2C%20or%20ABOVE%20and%20only%20populates%20if%20the%20Required%20and%20Current%20column%20have%20been%20populated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E3.%20Conditional%20Format%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EYou%20can%20modify%20the%20color%20scheme%20when%20managing%20the%20conditional%20format.%20There%20are%203%20based%20on%20the%20text%20returns.%20The%20trick%20to%20having%20the%20text%20hidden%20is%20to%20have%20the%20font%20match%20the%20background%20color%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_0-1608757505899.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242554i57EF7A0EC3E87616%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_0-1608757505899.png%22%20alt%3D%22adversi_0-1608757505899.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20copy%20and%20paste%20the%20TNA%20sample%20column%20to%20the%20others%20and%20the%20format%20and%20formulas%20will%20adjust.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2013568%22%20slang%3D%22en-US%22%3ERe%3A%20Conditions%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2013568%22%20slang%3D%22en-US%22%3EThx%20so%20much%20I%20really%20appreciate%20it.%3C%2FLINGO-BODY%3E
New Contributor
I have two tables, and I want to compare between them, and in table 3 the results of compared show in colors how I can do this?
4 Replies
best response confirmed by Isra_sidki (New Contributor)
Solution

@Isra_sidki 

Can you provide more explanation to the conditional format you are trying to setup? Is it the condition supposed to be less than a certain column or flag a certain text?

 

Also, please provide a sample workbook of how your data is structured.

This is the attachment I want the table of tna look just in color without any text.
Thx for the responding

@Isra_sidki 

See below how one of the TNAs are populated:

 

1. Assign values

I included a grading system (1-3) to make it easier for the TNA column to read the match requirements.

 

2. TNA Lookup Formula

 

=IF(OR(G14="",H14=""),"",IF(G14=H14,"MATCH",
IF(VLOOKUP(G14,Sheet1!$B$3:$C$5,2,FALSE)<VLOOKUP(H14,Sheet1!$B$3:$C$5,2,FALSE),"ABOVE","BELOW")))

 

This formula returns either MATCH, BELOW, or ABOVE and only populates if the Required and Current column have been populated.

 

3. Conditional Format

You can modify the color scheme when managing the conditional format. There are 3 based on the text returns. The trick to having the text hidden is to have the font match the background color

adversi_0-1608757505899.png

 

You can copy and paste the TNA sample column to the others and the format and formulas will adjust.

Thx so much I really appreciate it.