Home

Excel conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-854239%22%20slang%3D%22en-US%22%3EExcel%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854239%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20programming%20a%20sheet%20to%20conditionally%20format%20cells%20for%20certain%20values.%20I%20got%20it%20right%20with%20all%20the%20others%20but%20struggling%20with%20the%20following.%3C%2FP%3E%3CP%3EI%E2%80%99ve%20got%20a%20column%20that%26nbsp%3B%20uses%20Data%20Falidation%20to%20only%20allow%20M%20or%20F%20to%20indicate%20male%20or%20female.%20Other%20columns%20give%20test%20results.%20One%20of%20them%20are%20Waist%20Circumference.%20I%20need%20to%20format%20the%20Waist%20Circumference%20column%20to%20change%20colour%20for%20two%20different%20data%20sets%20%E2%80%93%20if%20marked%20M%20in%20the%20gender%20column%20every%20value%20greater%20or%20equal%20to%2094cm%20should%20change%20colour%20in%20the%20Waist%20Circumference%20column%20and%20if%20F%20in%20the%20gender%20column%20the%20value%20to%20change%20colour%20in%20the%20Waist%20Circumference%20column%20is%20greater%20or%20equal%20to%2080cm.%3C%2FP%3E%3CP%3EI%E2%80%99ve%20tried%20IF%20but%20seems%20because%20the%20Gender%20column%20is%20using%20Data%20Validation%20to%20only%20allow%20M%20or%20F%20it%20doesn%E2%80%99t%20allow%20me%20to%20use%20the%20IF%20function.%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E%3CSTRONG%3EGender(F%2FM)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EHeight%3C%2FSTRONG%3E%3CSTRONG%3E%20(M)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EWeight%20(kg)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EBMI%20%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%3CSTRONG%3EWaist%20(cm)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3EF%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E1.57%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E54.1%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E21.9%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E82%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-854239%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854249%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854249%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408413%22%20target%3D%22_blank%22%3E%40NJUys%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20below%20formula%20as%20conditional%20formatting%20rule%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131685iA674F96BC22BA7E4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DOR(AND(A2%3D%22M%22%2CE2%26gt%3B%3D94)%2CAND(A2%3D%22F%22%2CE2%26gt%3B%3D80))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20file%20is%20also%20attached%20for%20your%20reference.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854250%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408413%22%20target%3D%22_blank%22%3E%40NJUys%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20create%20two%20New%20Rules%20for%20conditional%20formatting%20using%20the%20formulas%20given%20below...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3EFor%20F%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DAND(%24A2%3D%22F%22%2C%24E2%26gt%3B%3D80)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3EFor%20M%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DAND(%24A2%3D%22M%22%2C%24E2%26gt%3B%3D94)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20applied%20the%20conditional%20formatting%20to%20the%20range%20A2%3AE1000%20so%20you%20can%20populate%20column%20A%20and%20column%20E%20with%20some%20dummy%20values%20to%20check%20whether%20the%20conditional%20formatting%20works%20as%20desired.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20applied%20the%20conditional%20formatting%20for%20whole%20row%20in%20the%20data%20set%20i.e.%20A%3AE.%20If%20you%20want%20to%20apply%20the%20conditional%20formatting%20just%20to%20column%20E%2C%20only%20select%20E2%3AE1000%20or%20whatever%20should%20be%20the%20last%20row%20and%20make%20new%20rules%20using%20the%20same%20formulas%20and%20set%20the%20format%20as%20per%20your%20choice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854251%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854251%22%20slang%3D%22en-US%22%3EThank%20you%2C%20will%20give%20it%20a%20go.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854252%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854252%22%20slang%3D%22en-US%22%3EThank%20you.%20Will%20try%20this%20method%20as%20well.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-854254%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-854254%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F408413%22%20target%3D%22_blank%22%3E%40NJUys%3C%2FA%3E%26nbsp%3BYou%20are%20welcome.%3C%2FP%3E%3C%2FLINGO-BODY%3E
NJUys
New Contributor

I am programming a sheet to conditionally format cells for certain values. I got it right with all the others but struggling with the following.

I’ve got a column that  uses Data Falidation to only allow M or F to indicate male or female. Other columns give test results. One of them are Waist Circumference. I need to format the Waist Circumference column to change colour for two different data sets – if marked M in the gender column every value greater or equal to 94cm should change colour in the Waist Circumference column and if F in the gender column the value to change colour in the Waist Circumference column is greater or equal to 80cm.

I’ve tried IF but seems because the Gender column is using Data Validation to only allow M or F it doesn’t allow me to use the IF function.

Gender(F/M)

Height (M)

Weight (kg)

BMI

Waist (cm)

F

1.57

54.1

21.9

82

 

 

 

 

 

 

Any suggestions?

5 Replies

Hi @NJUys 

You can use below formula as conditional formatting rule:

clipboard_image_0.png

 

=OR(AND(A2="M",E2>=94),AND(A2="F",E2>=80))

 

Sample file is also attached for your reference.

Thanks

Tauqeer

Highlighted

@NJUys 

You may create two New Rules for conditional formatting using the formulas given below...

 

For F:

=AND($A2="F",$E2>=80)

 

For M:

=AND($A2="M",$E2>=94)

 

I have applied the conditional formatting to the range A2:E1000 so you can populate column A and column E with some dummy values to check whether the conditional formatting works as desired.

 

I have applied the conditional formatting for whole row in the data set i.e. A:E. If you want to apply the conditional formatting just to column E, only select E2:E1000 or whatever should be the last row and make new rules using the same formulas and set the format as per your choice.

 

 

 

Thank you, will give it a go.
Thank you. Will try this method as well.

@NJUys You are welcome.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 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
29 Replies