Based on the eight column table (A:H) and with five conditions for the product type (in column B), this code will colour format the records, driven by the Worksheet_Change event. Because of the Select Case statement, you can have actually as many conditions as you wish:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim prodType As String
Application.EnableEvents = False
If Target.Column = 8 Then
ActiveCell.Offset(0, -7).Select
prodType = ActiveCell
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, 6)).Select
Select Case prodType
Case "ACID"
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Case "ALKALINE"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Case "SULPHONE"
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Case "NON-HAZ"
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Case "REBLEND"
With Selection.Interior
.ColorIndex = 12
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Select
ActiveCell.Offset(1, 0).Select
End If
Application.EnableEvents = True
End Sub