Conditional formatting (IF = RANGE)?

**simco** · 07-09-2019, 09:12 AM

Hi I'm a quality engineer and I need to prevent inspectors of our product entering results into the spreadsheet we use and then finding out it does not meet requirements after confirming the product is ok when it is actually not.

I'm looking for a formula to work with conditional formatting.

We have a specification for set products in our catalogue with set tolerances which will fall between a range. I want the cell to light up red when it falls outside this range.

To add to this, the range has to be based on a tolerance for a particular product which will be in another sell on the same row. Let me give you an example.

Product A has a tolerance between 100 - 200.
Product B has a tolerance between 150 - 250.

I guess the formula in my head would be something like this:
=IF Cell A5=Product A - Range = 175 - This would not trigger conditional formatting.
=IF Cell A5=Product A - Range = 135 - This would trigger conditional formatting.

Thanks so much in advance, this has been bugging me for months.

**63falcondude** · 07-09-2019, 09:15 AM

Hello and welcome to the forum.

I recommend uploading a small representative sample of your data along with the desired results (which you can color in manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

**Richard Buttrey** · 07-09-2019, 09:15 AM

Welcome to the forum.

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

**simco** · 07-09-2019, 09:27 AM

Ok brilliant, thanks for getting back to me. Two attachments should be added to this post.

The image attached should show an example.

I'm working from the sheet named "Sunday".
Where it says GRADE in AQ8, this is where the range data should look to see whether it is within specification. Each grade and it's range is on the "Cell Data" sheet.

The example shows that 500+ has a range of 170 - 210. Cell BD8 has a result of 175 (within range of 500+) but the cell BE8 is 240, (outside of the range specification for 500+). I want the conditional formatting to trigger in instances like this, outside of specification.

I hope this helps.

Regards

**Richard Buttrey** · 07-09-2019, 09:48 AM

Hi,

In BE 8 use the CF

Formula:

Please Login or Register  to view this content.

**63falcondude** · 07-09-2019, 09:49 AM

Try this:

Highlight BD8:BE20 > Conditional Formatting > New Rule > Use a formula
=OR(BD8< VLOOKUP($AQ8,'Cell Data'!$C:$E,2,0),BD8> VLOOKUP($AQ8,'Cell Data'!$C:$E,3,0))
Format: Fill red > OK > OK

Edit: Beat to it!

**simco** · 07-09-2019, 09:58 AM

Thats it! No messing about here hahah. Thanks so much, now I just need to go through each grade and do the same. Thank you both

**63falcondude** · 07-09-2019, 10:00 AM

Unless I am misunderstanding what you meant, the formulas provided will work for all grades.

You will have to apply the conditional formatting to all worksheets though.

**simco** · 07-09-2019, 10:05 AM

Yep as I thought re: each sheet.

You are correct, it does work for all grades?! I'm impressed, I thought it would be a much more basic formula but that has saved me a lot of time and headache! Thanks

**63falcondude** · 07-09-2019, 10:07 AM

It does work for all grades. VLOOKUP looks up the grade and returns the min and the max for that grade.

Glad we could help. Thanks for the rep!

**simco** · 07-09-2019, 10:08 AM

Ok, so I will probably want to add this kind of conditional formatting for the chemical analysis we generate for our results too in the future. Is there an easy way I can do the same thing for a table of chemical data ranges already available on another sheet, or change a particular part of that formula you supplied to work?

Thanks

**simco** · 07-09-2019, 10:09 AM

Thats so good. I'm very surprised it wasn't more complicated than that!

**63falcondude** · 07-09-2019, 10:17 AM

I don't know what's involved in your chemical analysis so I can't say whether or not this will work for that.

What I can do is explain how the formula in post #6 works. Understanding this should help you tell if you can use the same structure or not for your chemical analysis.

**simco** · 07-09-2019, 10:20 AM

It's the same principle, it's just numbers in a range.

Yep that would be great. I just need to work out which part of the formula I would need to replace with another two columns data ranges like in the original query, still using the same grade principle. Hope that makes sense?

**63falcondude** · 07-09-2019, 10:22 AM

=OR(BD8< VLOOKUP($AQ8,'Cell Data'!$C:$E,2,0),BD8> VLOOKUP($AQ8,'Cell Data'!$C:$E,3,0))

The first VLOOKUP looks for the value of AQ8 in column C of Cell Data, then returns the corresponding value (same row) in the 2nd column of C:E (which is column D).

The second VLOOKUP is the same except it returns the 3rd column of C:E (which is column E).

The OR function then checks if the value in BD8 is less than the min (the number returned by the first VLOOKUP), or is greater than the max (the number returned by the second VLOOKUP).

If either of these return TRUE, the OR function will return TRUE, and the Conditional Formatting will then be applied.

**simco** · 07-09-2019, 10:48 AM

Originally Posted by 63falcondude

=OR(BD8< VLOOKUP($AQ8,'Cell Data'!$C:$E,2,0),BD8> VLOOKUP($AQ8,'Cell Data'!$C:$E,3,0))

The first VLOOKUP looks for the value of AQ8 in column C of Cell Data, then returns the corresponding value (same row) in the 2nd column of C:E (which is column D).

The second VLOOKUP is the same except it returns the 3rd column of C:E (which is column E).

The OR function then checks if the value in BD8 is less than the min (the number returned by the first VLOOKUP), or is greater than the max (the number returned by the second VLOOKUP).

If either of these return TRUE, the OR function will return TRUE, and the Conditional Formatting will then be applied.

Thats much easier to understand, I think I'll be able to work it out for each different column now. Thanks, you've been a big help!

**63falcondude** · 07-09-2019, 11:15 AM

You're welcome. Happy to help.

Conditional formatting (IF = RANGE)?

LinkBack

Thread Tools

Rate This Thread

Display

Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Re: Conditional formatting (IF = RANGE)?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Apply Conditional Formatting to Custom Range if Cell Value Outside Range is ____

[SOLVED] Conditional formatting one range containing text from another range

Conditional formatting large range of cells, based on percentage of previous cell in range

Conditional Formatting via VBA: Change formatting in range based on value of each cell

[SOLVED] Applying Conditional Formatting to a Range in VBA (range is set by variables)

Conditional Formatting - Grey Out and Lock A range based on a cell value in that range

Conditional Formatting (How to format a range depending on another range)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions