Compare 2 lists

Oops! Just noticed you're after a VBA solution, not a formula

In D9
=IF(ISNA(VLOOKUP(B9,SourceList!C$9:C$12,1,0)),"Material is not in the Sourcelist","")
and copy down

To highlight the cells use Condtional Formatting

Select B9:D12

Conditional Formatting
New Rule
Use a formula to determine...

=ISNA(VLOOKUP($B9,SourceList!$C$9:$C$12,1,0))

Format as required

Try:

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, material As Range, rng As Range, foundMaterial As Range
    LastRow = Sheets("Summary").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set material = Sheets("Summary").Range("B9:B" & LastRow)
    For Each rng In material
        Set foundMaterial = Sheets("SourceList").Range("C:C").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If foundMaterial Is Nothing Then
            rng.Resize(, 3).Interior.ColorIndex = 3
            rng.Resize(, 3).Font.Color = vbWhite
            rng.Offset(0, 2) = "Material does not exist in the SourceList"
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

My pleasure.