Conditional formatting based on two quantity columns (Excel)

Question

I'll try to be as specific as possible.

I want to compare an array of different codes (Quantity Needed). A column that contains the same code multiple times, and each of these needs to be compared to the value of a single cell in another column, that contains that code only once (Stock Qty).

To be more specific,I need to compare the quantity needed of said specific code (which is located in a cell next to the code) to the quantity in stock of said code (which is located in another table).

Then I need said 'Quantity Needed" cell to turn either Green (if the number on the Stock Qty column related to that specific code is equal or bigger than the number on the Quantity Needed cells for that code) or Red (If the number is less).

I could do this with conditionals one by one but the amount of codes is very large.

Image for further comprehension: https://snag.gy/EnzGWc.jpg

Thanks.

Answer 1

You can use conditional formatting with formula. goto conditional formatting=>New rule=>formula. There enter this formula =D3-VLOOKUP($B3,$H$3:$I$10000,2,FALSE)<0 . Set applies to =$D$3:$D$10000. Adjust range to fit all values in columns.