Home

Conditional formatting based on values in either of 2 other cells.

%3CLINGO-SUB%20id%3D%22lingo-sub-477656%22%20slang%3D%22en-US%22%3EConditional%20formatting%20based%20on%20values%20in%20either%20of%202%20other%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477656%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20change%20the%20color%20of%20cell%20C%20if%20the%20value%20in%20either%20cell%20A%20or%20cell%20B%20%3D%205.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help!%3C%2FP%3E%3CP%3EKat%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-477656%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477664%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20values%20in%20either%20of%202%20other%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477664%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324439%22%20target%3D%22_blank%22%3E%40KatWarren%3C%2FA%3E%20%2C%20you%20may%20apply%20to%20your%20range%20(assume%20starts%20from%20C1)%20conditional%20formatting%20rule%20with%20formula%3C%2FP%3E%0A%3CPRE%3E%3D(A1%3D5)%2B(B1%3D5)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477669%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20values%20in%20either%20of%202%20other%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477669%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EYes%2C%20I%20am%20applying%20the%20range%20for%20the%20entire%20column.%20%26nbsp%3B%20It%20is%20actually%20more%20complicated%20even...%26nbsp%3B%20Here%20is%20the%20table...%20column%20D%20indicates%20the%20color%20I%20need%20column%20C.%3C%2FP%3E%3CP%3EA%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3Eyellow%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3Eyellow%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3Eyellow%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Eyellow%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477676%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20values%20in%20either%20of%202%20other%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477676%22%20slang%3D%22en-US%22%3E%3CP%3EActually%2C%20I%20think%20I%20can%20make%20this%20more%20simple%2C%20now%20that%20I%20did%20that...%20to%20just%20do%20conditional%20based%20on%20the%20product...%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324439%22%20target%3D%22_blank%22%3E%40KatWarren%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EYes%2C%20I%20am%20applying%20the%20range%20for%20the%20entire%20column.%20%26nbsp%3B%20It%20is%20actually%20more%20complicated%20even...%26nbsp%3B%20Here%20is%20the%20table...%20column%20D%20indicates%20the%20color%20I%20need%20column%20C.%3C%2FP%3E%3CP%3EA%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%20B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3Eyellow%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3Eyellow%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3Eyellow%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3Ered%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Eorange%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Eyellow%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Egreen%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477678%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20values%20in%20either%20of%202%20other%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F324439%22%20target%3D%22_blank%22%3E%40KatWarren%3C%2FA%3E%20%2C%20to%20apply%20the%20conditional%20formatting%20you%20need%20to%20have%20some%20formal%20logic%20for%20such%20conditions%2C%20after%20that%20convert%20it%20to%20formulas%20for%20the%20rules.%20I%20didn't%20catch%20what%20is%20the%20logic%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-477682%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20values%20in%20either%20of%202%20other%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-477682%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EYes.%26nbsp%3B%20The%20more%20I%20looked%20at%20it%2C%20the%20more%20I%20see%20the%20lack%20of%20pattern.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
KatWarren
New Contributor

I am trying to change the color of cell C if the value in either cell A or cell B = 5. 

 

Thanks for any help!

Kat

5 Replies

@KatWarren , you may apply to your range (assume starts from C1) conditional formatting rule with formula

=(A1=5)+(B1=5)

 

@Sergei BaklanYes, I am applying the range for the entire column.   It is actually more complicated even...  Here is the table... column D indicates the color I need column C.

A          B         C         C

5525red
5420red
5315red
5210orange
515yellow
4520red
4416red
4312orange
428yellow
414green
3515red
3412red
339orange
326yellow
313green
2510red
248red
236orange
224green
212green
155red
144orange
133yellow
122green
111green

Actually, I think I can make this more simple, now that I did that... to just do conditional based on the product...


@KatWarren wrote:

@Sergei BaklanYes, I am applying the range for the entire column.   It is actually more complicated even...  Here is the table... column D indicates the color I need column C.

A          B         C         C

5525red
5420red
5315red
5210orange
515yellow
4520red
4416red
4312orange
428yellow
414green
3515red
3412red
339orange
326yellow
313green
2510red
248red
236orange
224green
212green
155red
144orange
133yellow
122green
111green

 

@KatWarren , to apply the conditional formatting you need to have some formal logic for such conditions, after that convert it to formulas for the rules. I didn't catch what is the logic here.

@Sergei BaklanYes.  The more I looked at it, the more I see the lack of pattern. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies