Conditional Formatting

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

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
 
No real need for cod
Select the range and g
Format>Conditional 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
 
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
 
Well, we've only just moved to XP from 97 and I used to use it there too....so yes
95 (version 5 doesn't)
 
Back
Top