How to create data validation based on multiple roles?

Question

We have multiple roles in column L, like Admin, Clerk, Moderator, and User.

I would like to check if these roles are spelled correctly and if not it should highlight the cell red.

Is this possible?

I checked data validation, but that is not going to work since there are multiple roles in each cell. For example, we assign roles like Admin||Clerk||Moderator — Ulquiorra Schiffer, Jan 13 '21 at 13:28
Then use concatenate() or the & to join the selected values together C11&"||"&D11 etc — Solar Mike, Jan 13 '21 at 14:03

score 0 · Accepted Answer · answered Jan 14 '21 at 01:19

Data Validation Via Worksheet Change

To 'update' the colors, do a 'Copy/PasteValues' in column L.

Sheet Module (e.g. Sheet1)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const RolesList As String = "Admin,Clerk,Moderator,User"
    Const FirstCellAddress As String = "L2"
    Const Delimiter As String = "||"
    
    Dim rng As Range
    With Range(FirstCellAddress)
        Set rng = Intersect(.Resize(.Worksheet.Rows.Count - .Row + 1), Target)
    End With
    If rng Is Nothing Then
        Exit Sub
    End If
    
    Dim Roles() As String: Roles = Split(RolesList, ",")
    
    Dim dRng As Range
    Dim aRng As Range
    Dim cel As Range
    Dim Curr() As String
    Dim cMatch As Variant
    Dim n As Long
    Dim isFound As Boolean
    
    For Each aRng In rng.Areas
        For Each cel In aRng.Cells
            If Not IsError(cel) Then
                Curr = Split(cel.Value, Delimiter)
                For n = 0 To UBound(Curr)
                    cMatch = Application.Match(Curr(n), Roles, 0)
                    If IsError(cMatch) Then
                        isFound = True
                        Exit For
                    Else
                        ' Remove this block if you don't need case-sensitivity.
                        If StrComp(Curr(n), Roles(cMatch - 1), _
                                vbBinaryCompare) <> 0 Then
                            isFound = True
                            Exit For
                        End If
                    End If
                Next n
                If isFound Then
                    isFound = False
                    If dRng Is Nothing Then
                        Set dRng = cel
                    Else
                        Set dRng = Union(dRng, cel)
                    End If
                End If
            End If
        Next cel
    Next aRng
    
    Application.ScreenUpdating = False
    rng.Interior.Color = xlNone
    If Not dRng Is Nothing Then
        dRng.Interior.Color = vbRed
    End If
    Application.ScreenUpdating = True
    
End Sub

WOW I just saw your replay thanks man I'm going to check it out and see if it works !! — Ulquiorra Schiffer, Jan 17 '21 at 09:03

How to create data validation based on multiple roles?

1 Answers1

Data Validation Via Worksheet Change

Linked