Excel comparing two cells, conditional formatting.

Question

Question

I am trying to highlight a cell green, yellow, red depending on whether two cells meet or exceed limits set.

-The backstory, trying to take a health chart that gives height to ideal waist measurement, so when the user enters the two values into the different cells, it will highlight the waist measurement either green, yellow, or red.

example under E7 the user enters "68" for inches into the cell. In cell E9 the user enters "38". Ideally, a 68" male should have a 31.2" waist. 38" is considered obese and should be a red flag. Anything between 31.2 and 37 is yellow and lets the user know to start a diet.

How would I turn cell E9 to green, yellow, red based on this criteria?

Answer 1

I'd create a list like this - there is no column for "Ideal", and the columns for "Caution" and "Obese" contain the thresholds for each height.

In the following, the list is on the same sheet as the input in I2:K10, but it could just as well be on another sheet.

Select E9.

Set its fill color to green. This will be the default (for Ideal).

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'greater than or equal to' from the second drop down.

In the box next to it, enter the formula

=VLOOKUP(E7,$I$2:$K$10,2)

Click Format...

Activate the Fill tab.

Select yellow.

Click OK, then click OK again.

Repeat these steps, but this time with the formula

=VLOOKUP(E7,$I$2:$K$10,3)

and red as color.

Answer 2

Thanks Hans for the quick reply. Ideally the comparative analysis would look at what a user would enter as height (in a cell) and waist size (in a separate cell), then return conditional formatting green, yellow, red (ideally coloring the waist size cell).

height waist ideal (green) waist caution (yellow) Obese (red)

66 29 30-36 =>37

67 31 32-37 =>38

68 32 33-38 =>39

69 33 34-39 =>40

70 34 35-40 =>41

71 35 36-41 =>42

72 36 37-42 =>43

73 36 37-44 =>45

74 36 37-45 =>46

I would rather not want to use VBA if it could be helped, sending a VBA encoded Excel in corporate world gets flagged and not sent sometimes. If it can not be any other way, would go with VBA option. Can Excel do this and what would this complex formula look like?

Answer 3

Do you have a table with the thresholds for different heights? Or do you have a formula for the thresholds depending on the height?