基于两个数量列的条件格式（Excel）

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

图片进一步理解：https：//snag.gy/EnzGWc.jpg

Thanks.

谢谢。

1 个解决方案

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

您可以使用带公式的条件格式。转到条件格式=>新规则=>公式。输入这个公式= D3-VLOOKUP（$ B3，$ H $ 3：$ I $ 10000,2，FALSE）<0。 Set适用于= $ D $ 3：$ D $ 10000。调整范围以适合列中的所有值。