Excel Conditional Formatting over multiple rows

2
votes

I am trying to highlight cells that fall outside the expected value range (higher or lower only). Each row corresponds to a different row that has two cells with a max and min. Is there a way to make Excel figure that out?

For example Row 7's cells should only highlight if they value is outside the min of B31 or max B32. And Row 18 should be governed by Row 42.

enter image description here

I have used conditional formatting. But I want to apply the same logic to all the cells and rows instead of doing them one by one.

enter image description here

1
excelformatting
Use VLOOKUP() to find the min and max based on column A. - Scott Craner
Ever considered using VBA...? - ManishChristian

1 Answers

1
votes

Scott Craner's suggestion is spot on. You can use a formula inside of your conditional formatting by following the instructions below:

  1. Select Cell B4 (Sodium baseline test results)
  2. Click "Conditional Formatting" in the Ribbon, "Highlight Cells Rules", and then "Less Than"
  3. In the dialog that pops up, enter the following formula:

=VLOOKUP(A4,$A$27:$C$48,2,FALSE) Adding vlookup formula to conditional formatting

  1. Click OK to save and apply the conditional format.
  2. Click the B4 Cell again, and select the bottom right corner (you should see a little box sort of that you can click and drag).

  3. RIGHT CLICK AND DRAG (not left click/drag) that bottom corner so that all the cells get selected. Then when the dialog box pops up, select "Fill Formatting Only". This will apply the formula to all the cells, and the lookup conditions automatically update based on the test name in the same row. Select and drag to apply conditional formatting to all cells

  4. Repeat the above for the Greater Than rules, but modify the lookup formula as follows so that it looks up the MAX (3rd) Column:

=VLOOKUP(A4,$A$27:$C$48,3,FALSE)

If this answer was useful or helpful, please mark or indicate as such, thank you!