Conditional Formatting with multiple conditions

[defremat]

Hi,
Hoping someone can help with what is probably pretty basic conditional formatting.

I have a table which includes a 7 digit number (this represents a material), and for each material number there is a upper limit and a lower limit (also a number). The upper and lower limit indicate a range which a test result must fall between. There about 100 different material numbers, each with different upper and lower limits.

On a second tab, I want to record the results of each test. In cell A, I would record the material number the test relates to, and in column B I would record the actual rest result. If the test result falls within the upper and lower limit for the specific material, I want it to turn green. If it falls outside, I want it to turn red. In other words, when i record a test result, I want it to pick up the upper and lower limits for the specific material and tell me if it falls between the upper and lower limits.

Hoping someone can help with the formula I would need to use for this.

Thank you in advance for your help!

NB. The upper and lower limits may change from time to time so I need the formula to be able to manage this without having to manually update the formula.

Matt

[AliGW]

Welcome to the forum.

Please update your profile with your version of Excel - 10 is Windows, not MS Office. Thanks.

There are instructions at the top of the page telling you how to attach a sample workbook.