VBA Help - on Visible cells

[malleshg24]

Hi Team,


I want to search multiple text in a visible cell by using sumproduct formula, if found then in immediate columns next cell I want "DATA Found text.


else leave blank. My attempted code. I have huge data above (1.5 lacs row) . plz assist for below code or any other option ok for it. Thanks in advance !!!


Sub Test()


Dim lr As Long
Dim c As Range


lr = Range("a1").CurrentRegion.Rows.Count
With Sheet1
If .AutoFilterMode = True Then .AutoFilterMode = False
.Range("a1").CurrentRegion.AutoFilter field:=2, Criteria1:=""
If Application.WorksheetFunction.Subtotal(3, .Range("B2:B" & lr).SpecialCells(xlCellTypeVisible)) > 1 Then
For Each c In .Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
if c.value = SUMPRODUCT(--ISNUMBER(SEARCH({""red"",""blue"",""green""},c.value)))>0 then
c.Offset(, 1).Value = found
Next c
End If
End With
End Sub

Regards,
Mallesh