Search a Range for a phrase and Format cell if found

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

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

<[email protected]> wrote in message
news:[email protected]...
> 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

Thanks for the quick reply that worked a treat.

Kev

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)


<[email protected]> wrote in message
news:[email protected]...
> 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