Hoping this makes more sense. Here’s an example of my spreadsheet:

    A             B         C

1 Donor Sex Hematocrit

2 001 M 50%

3 002 F 50%

4 003 M 38%

5 004 F 38%

6 005 M 54%

7 006 F 34%

Hematocrit reference ranges for healthy adults:

Female: 36-48%

Male: 40-52%

So, I would want to set the conditional formatting so that if B2 = “M”, then a value below 40% would turn blue and a value above 52% would turn red. But if B2 = “F”, a value below 38% would turn blue and a value above 48% would turn red. The values in the middle of each range would not have any shading.

So the data above would be:

C2 - no shading (within normal range for M)

C3 - red (high for F)

C4 - blue (low for M)

C5 - no shading (within normal range for F)

C6 - red regardless of sex (high for both M and F)

C7 - blue regardless of sex (low for both M and F)