Adding a variable to index & match function using VBA

Hi prestopr,

I've altered your code but am unable to test the coloring aspect since I'm using 2003 and it protests against the "too many formats" in your sample - let me know if it works for you:

Sub prestopr(): Dim wc As Worksheet, wr As Worksheet, Product_List As Range
Set wc = ActiveWorkbook.Sheets("Chimi"): Set wr = ActiveWorkbook.Sheets("Ref_Tables")
' Checks that all grade variables are within the defined material type's grade ranges.
If wc.Range("A5") = "" Then
       MsgBox "There is no data to check" & Chr(13) & Chr(13)
        Exit Sub: End If
    
Set Product_List = wc.Range("Block_ID")
    
'-------------------------------------------------------------
Dim mat_ox As String, Record As Range, i As Integer
Dim CU_LL As Single, CU_UL As Single, U_LL As Single
Dim U_UL As Single, SG_LL As Single, SG_UL As Single

For Each Record In Product_List
mat_ox = Record.Offset(0, 1) + Record.Offset(0, 2)
For i = 4 To 23
If mat_ox = UCase(wc.Range("C" & i) & wc.Range("D" & i)) Then

CU_LL = wr.Range("E" & i): CU_UL = wr.Range("F" & i): U_LL = wr.Range("G" & i)
U_UL = wr.Range("H" & i): SG_LL = wr.Range("I" & i): SG_UL = wr.Range("J" & i)

Exit For: End If: Next i

    'MsgBox "Mat_ox = " & mat_ox & vbNewLine & _
    '       "Cu_LL = " & CU_LL & vbNewLine & _
    '       "Cu_UL = " & CU_UL & vbNewLine & _
    '       "U_LL = " & U_LL & vbNewLine & _
    '       "U_UL = " & U_UL & vbNewLine & _
    '       "SG_LL = " & SG_LL & vbNewLine & _
    '       "SG_UL = " & SG_UL
    '
           
If Record.Offset(0, 7) >= CU_LL And Record.Offset(0, 7) <= CU_UL Then
    MsgBox "Cu fails"
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
       
If Record.Offset(0, 7) >= U_LL And Record.Offset(0, 7) <= U_UL Then
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
       
If Record.Offset(0, 7) >= SG_LL And Record.Offset(0, 7) <= SG_UL Then
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
       
       
       
       
       
       Next
'--------------------------------------------------------------

    
End Sub