lets assume Material is column A and row 1. Lets start by making a formula that will do the check and then we can apply that formula to conditional formatting which will take care of the highlighting aspects
In this case I would look at the SUMIF() function. It take the following format:
SUMIF(RANGE OF CELLS TO CHECK, WHAT THE CHECK IS, RANGE OF CELLS TO SUM)
So in your case the formula would look something like:
=SUMIF($A:$A,$A2,$C:$C)
That will get you the total number of material A2 and so on as its copied down. What you need to do now is compare that to the amount in stock for a true false condition. in this case you want to trigger when stock is less than the required amount. You could set up your formula as follows:
=SUMIF($A:$A,$A2,$C:$C)>$D2
That should evaluate to TRUE.
Select the range of cell you want to apply this to and make sure the top left cell is the active cell of the selection set. Go to the Home Ribbon and head on over to the Style portion and select conditional formatting;
From the menu that will pop up select "New Rule" and enter your formula in the box in the middle. Click the format button and select all the formatting you want to apply when the formula evaluates to TRUE.
From the window that comes up select "Use a formula..."
UPDATE: What The Check?
What the check is, is a boolean expression. It will normally be one of =, <, >, >=, <= or <>. Now by default if no comparison string is added it is treated as = by default. In order to add a comparison you tend to do something like ">"&cell reference. You formula is actually:
=SUMIF($A:$A,"="&$A2,$C:$C)>$D2
