Search a Range for a phrase and Format cell if found

[Leaper]

Hi
I've got a spread sheet containing spec's for vehicles, I want to search
them to highlight specific items by changing the cell color

Here's an example of the sheet, this is one column with each line in a cell.

(BYRAB) PROGRAMMABLE DRIVER SEAT
(CBLAE) 315 MHZ DOOR LOCK FREQUENCY
(CFKAA) LESS HEADLAMP CLEANING
(CBSAC) SINGLE LOCKING PWR LOCK LATCH
(DAQAA) LESS BLOCK HEATER RECEPTACLE

I need to highlight any that have "LESS" ,there are 77 columns vehicle of
data by 100 rows

would be gratefully for any help

Kev

 

[RB Smissaert]

This is one way of doing it:

Sub test()

Dim arr
Dim r As Long
Dim c As Long

arr = Range(Cells(1), Cells(100, 77))

For r = 1 To 100
For c = 1 To 77
If InStr(1, _
arr(r, c), _
" LESS ", _
vbBinaryCompare) > 0 Then
Cells(r, c).Interior.ColorIndex = 27
End If
Next
Next

End Sub


RBS

 

[Leaper]

Thanks for the quick reply that worked a treat.

Kev

 

[Bob Phillips]

Why not use conditional formatting.

Select all the cells and test for a value of LESS, and select a pattern
colour.

--

HTH

RP
(remove nothere from the email address if mailing direct)