I am using Excel 97 and want to colour a cell if it
includes the word say for example 'business' somewhere in
the text of that cell.

Checking each cell in a column of lets say a 1000 entries

Can anyone assist me with some code if this is possible
please?


Thanks

Mark

The follwoing code looks in a sheets range
called 'MyRange' ...

Sub ColorTest()

Dim Trgt As Range
Dim firstadd As String
Set Trgt = ThisWorkbook.Names
("MyRange").RefersToRange.Find("business")


If Not Trgt Is Nothing Then
firstadd = Trgt.Address
Do
Trgt.Interior.ColorIndex = 34
Set Trgt = ThisWorkbook.Names
("MyRange").RefersToRange.FindNext(Trgt)
Loop Until Trgt.Address = firstadd
End If


End Sub

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
I am using Excel 97 and want to colour a cell if it
includes the word say for example 'business' somewhere
in
the text of that cell.

Checking each cell in a column of lets say a 1000 entries

Can anyone assist me with some code if this is possible
please?


Thanks

Mark
.

No real need for cod
Select the range and g
FormatConditional Formattin
Change the left dropdown to "Formula Is" andenter
=FIND("BUSINESS",UPPER(A1)
where the 1st cell in the range is A1

Patrick,

For some reason when I run this code I get the following
error at this point

Set Trgt = ThisWorkbook.Names
("MyRange").RefersToRange.Find("business")

Run-time 1004
Application-defined or object-defined error

Any ideas?

Mark


-----Original Message-----
The follwoing code looks in a sheets range
called 'MyRange' ...

Sub ColorTest()

Dim Trgt As Range
Dim firstadd As String
Set Trgt = ThisWorkbook.Names
("MyRange").RefersToRange.Find("business")


If Not Trgt Is Nothing Then
firstadd = Trgt.Address
Do
Trgt.Interior.ColorIndex = 34
Set Trgt = ThisWorkbook.Names
("MyRange").RefersToRange.FindNext(Trgt)
Loop Until Trgt.Address = firstadd
End If


End Sub

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
I am using Excel 97 and want to colour a cell if it
includes the word say for example 'business' somewhere
in
the text of that cell.

Checking each cell in a column of lets say a 1000 entries

Can anyone assist me with some code if this is possible
please?


Thanks

Mark
.

.

Patrick,

Are you sure you have a range named "MyRange"? Does the following
work?

Set Trgt = Range("MyRange").Find("business")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

wrote in message
...

Patrick,

For some reason when I run this code I get the following
error at this point

Set Trgt = ThisWorkbook.Names
("MyRange").RefersToRange.Find("business")

Run-time 1004
Application-defined or object-defined error

Any ideas?

Mark


-----Original Message-----
The follwoing code looks in a sheets range
called 'MyRange' ...

Sub ColorTest()

Dim Trgt As Range
Dim firstadd As String
Set Trgt = ThisWorkbook.Names
("MyRange").RefersToRange.Find("business")


If Not Trgt Is Nothing Then
firstadd = Trgt.Address
Do
Trgt.Interior.ColorIndex = 34
Set Trgt = ThisWorkbook.Names
("MyRange").RefersToRange.FindNext(Trgt)
Loop Until Trgt.Address = firstadd
End If


End Sub

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
I am using Excel 97 and want to colour a cell if it
includes the word say for example 'business' somewhere
in
the text of that cell.

Checking each cell in a column of lets say a 1000 entries

Can anyone assist me with some code if this is possible
please?


Thanks

Mark
.

.

does XL97 have conditional formatting?

-----Original Message-----
No real need for code
Select the range and go
FormatConditional Formatting
Change the left dropdown to "Formula Is" andenter:
=FIND("BUSINESS",UPPER(A1))
where the 1st cell in the range is A1
.