56969

Excel Conditional Formatting over multiple rows

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.

<img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/G1qhN.png" alt="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.

<img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/jgqwh.png" alt="enter image description here">

Answer1:

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

=VLOOKUP(A4,$A$27:$C$48,2,FALSE) <img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/Y3wy8.png" alt="Adding vlookup formula to conditional formatting">

<ol start="4"> <li>Click OK to save and apply the conditional format.</li> <li>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).</li> <li>

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>

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

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

Recommend

  • Sending Mail from Windows Azure Service, using Godaddy SMTP [closed]
  • Windows authentication hybrid
  • Silverlight and binding to anonymous types
  • Bootstrap fixed navigation jumps when scrolling
  • how to handle large size of update query in mysql with laravel
  • How to show a specific VC , more like navigate to a stack of VC
  • How to delete column from range if cell contains specific value in VBA/Excel
  • Edit assembly language code in Visual Studio while stepping through each statement
  • What exactly is the height of modalPresentationStyle - FormSheet on iPad?
  • Adding independent aspx/asmx pages into DotNetNuke
  • calculating number of bytes of each row in an image
  • Excel distinct count with conditions
  • How to create a Unix-domain socket with specific access permissions
  • Upgrade project from WorkLight 6.1 to MobileFirst 7.1
  • F#: In which memory area is the continuation stored: stack or heap?
  • Getting the scrolling offset when storing coordinates
  • how do i write assembly code from c#?
  • Debugging VB6 Code From Visual Studio 2010
  • xtable package: Skipping some rows in the output
  • Sort List of Strings By Version
  • onBackPressed() not being executed
  • MongoDb aggregation
  • Set the selected item in dropdownlist in MVC3
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • jQuery .attr() and value
  • Highlight one bar in a series in highcharts?
  • Getting last autonumber in access
  • How to check if every primary key value is being referenced as foreign key in another table
  • MySQL WHERE-condition in procedure ignored
  • ActionScript 2 vs ActionScript 3 performance
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • Do I've to free mysql result after storing it?
  • How to get next/previous record number?
  • Transpose CSV data with awk (pivot transformation)
  • WPF Applying a trigger on binding failure
  • How do you join a server to an Active Directory (domain)?
  • Why joiner is not used after Sequence generator or Update statergy
  • How does Linux kernel interrupt the application?
  • Sorting a 2D array using the second column C++