Sirspams
0
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
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
