• 466K Members
• 7,997 Online
• 563K Conversations

New Contributor

# Excel conditional formatting

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

# Re: Excel conditional formatting

Hi @NJUys

You can use below formula as conditional formatting rule:

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

Sample file is also attached for your reference.

Thanks

Tauqeer

# Re: Excel conditional formatting

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.

# Re: Excel conditional formatting

Thank you, will give it a go.

# Re: Excel conditional formatting

Thank you. Will try this method as well.
Highlighted

# Re: Excel conditional formatting

@NJUys You are welcome.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies