vba , higlight cells if it contains anything from criteria

Mallesh23

Board Regular
Joined
Feb 4, 2009
Messages
201
Hi Team,

Below code works for exact cells match for list of Array value,
Now I am looking for Contains, instead of exact match.

Example-
if Array value Contain Red, and Cell value Contain Redish,
then Still it has bold that cell. Because Red is Contained in cell value....



Sub test()
Dim ar As Variant
ar = WorksheetFunction.Transpose(Sheet1.Range("G2:g5").Value) 'array("Green",Red","Yellow"
or
' ar = Sheet1.Range("G2:g5").Value)

Dim lr As Long
Dim i As Long

lr = Sheet1.Range("a1000").End(xlUp).Row
For i = 2 To lr 'List of Color
If IsNumeric(Application.Match(Sheet1.Cells(i, 1).Value, ar, 0)) Then
Sheet1.Cells(i, 1).Font.Bold = True
End If
Next i

End Sub


Regards,
mg
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,710
Office Version
2007
Platform
Windows
Try this

VBA Code:
Sub test()
  Dim r As Range, lr As Long, i As Long
  Set r = Sheet1.Range("G2:G5")
  lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
  For i = 2 To lr 'List of Color
    If Evaluate("=SUM(IF(ISNUMBER(SEARCH(" & r.Address & "," & Sheet1.Range("A" & i).Address & ")),1,0))") > 0 Then
      Sheet1.Range("A" & i).Font.Bold = True
    End If
  Next i
End Sub
 

Forum statistics

Threads
1,077,778
Messages
5,336,248
Members
399,072
Latest member
abublitz

Some videos you may like

This Week's Hot Topics

Top