How to sets a range of cells as condition for If Statement in Excel VBA

You're on the right track with the idea to store the supplier names in their own sheets. Your best bet is to encapsulate the logic that determines if a supplier requires special treatment into its own function. This will help to clean up your main method, and keeps all relevant logic grouped together.

I'll give an example below. I created a function DoesSupplierRequireComment that takes a supplier name, and returns True/False depending on whether the supplier is in column A of sheet Variables. Then, you can simply call the function from your main method, and act as needed. (In the example below, I call the function from Sub Test and simply show a message box). See if you can adapt the logic into your code, and follow up with any questions.

Function DoesSupplierRequireComment(supplierName As String) As Boolean
    Dim isRequired As Boolean
    Dim searchRange As Range
    Dim lastRow As Long
    
    'Initialize variable to false
    isRequired = False
    
    With ThisWorkbook.Sheets("Variables")
        'Determine the range that stores the variables.
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        Set searchRange = .Range("A1:A" & lastRow)
        
        'Check if the passed in argument is found in the variables sheet
        If Not searchRange.Find(supplierName) Is Nothing Then
            isRequired = True
        End If
        
    End With
    
    DoesSupplierRequireComment = isRequired
End Function

Sub Test()
    If DoesSupplierRequireComment("LOL") Then
        MsgBox "Requires special treatment"
    Else
        MsgBox "Does Not"
    End If
End Sub

Worksheet_Change will run each time you change a cell. So, you only need it to make changes to cells in the same row as you the end user just changed. You are currently looping through all of ranges Rg1 and Rg3 with each cell change, which is undesirable.

From your description, I cannot tell what is in the various ranges. For this explanation:

• A2:A4 contains a list of special suppliers, which you should move to a separate worksheet from the sheet containing the main data table.
• A8:A9 contains the list of special materials (Cardboard and Toolkit), which you should move to a separate worksheet from the sheet containing the main data table.
• Columns B through D contain the data:
  • Column B: Alert
  • Column C: Supplier
  • Column D: Material

In this scenario, whenever we update a cell in columns B through E (except the first row), we want to check the supplier (col C) and material (col D). If the supplier matches an entry in A2:A4, then highlight it and add a hyperlink in Column B. If the material matches a material in A8:A9, then highlight it.

You can really do all of this (except inserting a hyperlink) without VBA by using VLookup and Conditional Formatting. However, I'll address the VBA approach, as it is worthwhile.

I will use Format as Table to address the issue of a potentially changing or growing list of conditions (Suppliers and Materials) that you may want to trigger the check. Format as Table is not about formatting, but about dynamically sizing tables and calling on their data much like a database. You can read a bit here: https://www.intheblack.com/articles/2018/08/01/understand-excel-format-as-table-icon. The Format as Table feature will allow you to change the number of conditions, which you refer to as Cells A2:A5 without having to change any code! Let's look at an example.

1. On a different sheet than your main data sheet, in Range A1 enter the title "Special Suppliers"
2. In range A2:A5 list your 4 special conditions that result in highlighting.
3. Select A1:A5.
4. From the Home ribbon, select Format as Table. When prompted, check the box to indicate the table has a header.
5. This is now named range 'Table1'. Let's go to the Table Design ribbon, and change the name to 'SpecialSupplierTable'.
6. Repeat steps1-5 to create a 'SpecialMaterialTable' with a header in C1 and data in C2:C3.
7. On the main data sheet, Highlight all of the main data table and Format as Table with name 'DataTable'.

You can now add and remove rows to any of these ranges freely. As you do, the named range will automatically resize. No need to change your code!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Excel automatically sets the Target argument.
    ' Target is a Range object that references the changed Cell.
    Dim msg As String
    
    
    ' Don't remove this, or the next failure will disable event handling,
    ' which means the macro will not run again.
    On Error GoTo EH
    
    ' It is important to disable and reenable event handling.
    Application.EnableEvents = False
    
    
    ' Only run this if the changed data is within the confines
    ' of the DataTable.
    If RangeAContainsRangeB([DataTable], Target) Then
    
        Call ApplyAlertFormatting(Target)
        
    End If
    
    Application.EnableEvents = True
    
    Exit Sub
EH:
    ' In the even of an error, enable event handling.
    Application.EnableEvents = True
    ' If you really want, you can comment out the next line
    ' to prevent error messaging, but I don't recommend it.
    Title = "Error " & Err.Number
    msg = "Error Sheet1.Worksheet_Change().  Error " & Err.Number & ": " & Err.Description
    MsgBox msg, vbCritical, Title, Err.HelpFile, Err.HelpContext
End Sub


Function ApplyAlertFormatting(Target As Range)
    Dim Supplier As Range
    Dim Material As Range
    Dim alertCell As Range
    Dim supplierCell As Range
    Dim materialCell As Range

    Set alertCell = Cells(Target.Row, [DataTable[Alert]].Column) ' Column B, the same row as the updated cell
    Set supplierCell = Cells(Target.Row, [DataTable[Supplier]].Column) ' Column C, the same row as the updated cell
    Set materialCell = Cells(Target.Row, [DataTable[Material]].Column) ' Column D, the same row as the updated cell
    
    ' If supplier on this row is found in SpecialSupplierTable
    If IsInRange(supplierCell.Value, Range("SpecialSupplierTable")) Then
        ' Change fill color of supplier cell
        supplierCell.Interior.Color = 24567
        ' Add hyperlink to Alert Window Row of cell that changed.
        With ActiveSheet.Hyperlinks.Add(alertCell, Address:="https://www.automateexcel.com/excel/", TextToDisplay:="Checking needed")
            .Range.Font.Bold = True
            .Range.Font.Underline = False
            .Range.Font.Color = vbMagenta
        End With
    End If
    
    If IsInRange(materialCell.Value, Range("SpecialMaterialTable")) Then
        MsgBox "Please send an email to notify", vbOKOnly + vbExclamation, "Quality Alert"
        materialCell.Interior.Color = 2552550  'Highlight the cells with Yellow Colour
    Else
        materialCell.Interior.Color = 16777215  'Clear the Colour in the cells if the value is nothing
    End If

End Function

The next two functions should not be placed in a sheet object code but in a Module, So that they can be used for other sheets.

Option Explicit

Public Function IsInRange(ByVal valueToFind, ByVal RangeToSearch As Range)
    ' If any cell in RangeToSearch = valueToFind, return True
    ' Else return False.
    IsInRange = True
    
    On Error GoTo EH
    x = RangeToSearch.Find(valueToFind)
    On Error GoTo 0

Exit Function
EH:
    If Err.Number = 91 Then
        ' this error is expected if valueToFind is not in RangeToSearch
        IsInRange = False
        Err.Clear
    Else
        ' Unexpected error.
        Err.Raise Number:=Err.Number, Source:=Err.Source, Description:=Err.Description
    End If
End Function


Public Function RangeAContainsRangeB(RangeA As Range, RangeB As Range)
' Returns True if RangeB is entirely within RangeA, else returns False.
    Dim rng As Range
    Set rng = Intersect(RangeB, RangeA)
    RangeAContainsRangeB = False
    If rng Is Nothing Then
        Exit Function
    ElseIf RangeB.Address = RangeA.Address Or rng.Address = RangeB.Address Then
        ' RangeB is completely within RangeA
        RangeAContainsRangeB = True
    End If
End Function