Excel conditional formatting

[NJUys]

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?

[#2]

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

[#3]

@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.

[#4]

Thank you, will give it a go.

[#5]

Thank you. Will try this method as well.

[#6]

@NJUys You are welcome.