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.
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.
<strong>Scott Craner's suggestion is spot on.</strong> You can use a formula inside of your conditional formatting by following the instructions below:<ol> <li>Select Cell B4 (Sodium baseline test results)</li> <li>Click "Conditional Formatting" in the Ribbon, "Highlight Cells Rules", and then "Less Than"</li> <li>In the dialog that pops up, enter the following formula:</li> </ol>
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. <img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/jJQRt.png" alt="Select and drag to apply conditional formatting to all cells"></li> <li>
Repeat the above for the Greater Than rules, but modify the lookup formula as follows so that it looks up the MAX (3rd) Column:</li> </ol>
If this answer was useful or helpful, please mark or indicate as such, thank you!