CODEWITHSUNDEEP
excelvba
Sirspams
Sirspams

Reputation: 11

VBA check list for multiple specific texts

Could someone help with a VBA macro code in excel to check that each individual column from D:AC contain

2 X *specific text A* AND

2 X *specific text B*

If even one of the four are missing then that columns row 3 cell interior.colourindex = 2 else interior.colourindex = 3

Upvotes: 1

Views: 33

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

Sub ColorIndexIt()
    Const specific_text_A = "AAA"
    Const specific_text_B = "BBB"
    Dim col As Range
    
    For Each col In ThisWorkbook.Sheets(1).Columns("D:AC") ' customize
        Intersect(col, col.Parent.Rows(3)).Interior.ColorIndex = _
            IIf(FindValueCount(specific_text_A, col) >= 2 And FindValueCount(specific_text_B, col) >= 2, 3, 2)
    Next
End Sub

Function FindValueCount(what As String, rng As Range) As Integer
    Dim c As Range, firstAddress As String
    
    With rng
        Set c = .Find(what, LookIn:=xlValues, lookat:=xlPart)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                FindValueCount = FindValueCount + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Function

enter image description here

Upvotes: 1

Related Questions