這邏輯不算難
只是做苦工而已

規則6的「即時檢查」有實作困難
因為
除非你能「一口氣輸入J3到J10的值」
否則
你永遠通過不了自己的檢查

<pre class="c" name="code">
Option Explicit
Const cTotalCountEmployee = 8   '員工總人數限制
Sub Main()
    If IsValid() Then
        MsgBox "檢查結果是符合規則的", vbInformation
    Else
        MsgBox "檢查結果是不符規則的", vbInformation
    End If
End Sub
Function IsValid() As Boolean
    Dim bRet As Boolean
    Dim arrType(1 To 4) '各組代號
    Dim arrMax(1 To 4)  '各組可允許最多人數
    Dim arrMin(1 To 4)  '各組可允許最少人數
    Dim arrCount(1 To 4) '各組小計人數
    Dim nI As Integer
    Dim nCountEmployee As Integer '統計總人數
    
    'Init values
    bRet = True
    nCountEmployee = 0
    arrType(1) = Range("B4").Value
    arrType(2) = Range("B5").Value
    arrType(3) = Range("B6").Value
    arrType(4) = Range("B7").Value
    
    arrMax(1) = Range("C4").Value
    arrMax(2) = Range("C5").Value + arrMax(1)
    arrMax(3) = Range("C6").Value + arrMax(1) + arrMax(2)
    arrMax(4) = Range("C7").Value + arrMax(1) + arrMax(2)
    
    arrMin(1) = 0
    arrMin(2) = 0
    arrMin(3) = Range("C6").Value
    arrMin(4) = Range("C7").Value
    
    For nI = 1 To 4
        arrCount(nI) = 0
    Next
    
    '統計各組人數
    For nI = 3 To 3 + cTotalCountEmployee - 1
        Select Case Range("J" & nI).Value
            Case arrType(1):
                arrCount(1) = arrCount(1) + 1
                nCountEmployee = nCountEmployee + 1
            Case arrType(2):
                arrCount(2) = arrCount(2) + 1
                nCountEmployee = nCountEmployee + 1
            Case arrType(3):
                arrCount(3) = arrCount(3) + 1
                nCountEmployee = nCountEmployee + 1
            Case arrType(4):
                arrCount(4) = arrCount(4) + 1
                nCountEmployee = nCountEmployee + 1
        End Select
    Next
    
    Debug.Print arrCount(1), arrCount(2), arrCount(3), arrCount(4)
    
    '檢查各項規則
    '規則1 - C4..C7 加起來等於 8
    If bRet = True Then
        If Range("C4").Value + Range("C5").Value + Range("C6").Value + Range("C7").Value = cTotalCountEmployee Then
            bRet = True
        Else
            bRet = False
        End If
    End If
    
    '規則2 - A 組
    If bRet = True Then
        If arrCount(1) >= arrMin(1) And arrCount(1) <= arrMax(1) Then bRet = True Else bRet = False
    End If
    
    '規則3 - B 組
    If bRet = True Then
        If arrCount(2) >= arrMin(2) And arrCount(2) <= arrMax(2) Then bRet = True Else bRet = False
    End If
    
    '規則4 - C 組
    If bRet = True Then
        If arrCount(3) >= arrMin(3) And arrCount(3) <= arrMax(3) Then bRet = True Else bRet = False
    End If
    
    '規則5 - D 組
    If bRet = True Then
        If arrCount(4) >= arrMin(4) And arrCount(4) <= arrMax(4) Then bRet = True Else bRet = False
    End If
    
    '規則 6 - J3..J11 加起來等於 8, 其實是判斷「是否 J3..J11 所有值都是合法值」
    If bRet = True Then
        If nCountEmployee = cTotalCountEmployee Then
            bRet = True
        Else
            bRet = False
        End If
    End If
    
    IsValid = bRet
End Function