changing cell color if cell has text

[Hein]

Hi
In parts of a large file I have to look for cells that have text i
them.
Examples of this text: PR, PB, SB, WR, NR, fall etc

I've tried the Conditional format but that doesn't seem to do the tric
(and is limited to 3 conditions) and the search function from this sit
doesn't seem to work?

In some cases there is only text in the cell, in other cases there wil
be numbers, as well as text
I have to do the search in a lot of files and I presume that the lis
of words/letters to look for will grow.
However: if I know how to get started I think I can manage

Thanks for your help in advance
Hei

 

[Zack Barresse]

Hi there Hein,

If you setup your data list on a seperate sheet and named the range which it
resides in, or even the entire column, you can still use one single
conditional format such as (assuming the cell is A1 and you named the range
"MyRange")...

=ISNA(MATCH(A1,MyRange,0))

Now you should have 2 more free conditional formats (if I understood you
correctly).

HTH

 

[Guest]

Have you tried the AutoFilter, and/or the AdvancedFilter?.......could be they
would help a lot, .......also, if all these strings are in one column, you
might could just sort on that column and that would group the text......

hth
Vaya con Dios,
Chuck, CABGx3

 

[Hein]

Thanks for your reply Zack,

The reason why I mentioned the Conditional formatting is that I am
looking for a way to get it done, and I thought that this was a
possibility.

I thought about a solution which goes something like

I choose an area to "investigate" and then start a VBA procedure which
goes something like


Code:
--------------------

dim c as range
for each c in selection.cells
if c = DQ or c = "*DQ*" then
cell color = yellow
else
cell color = normal
end if
next c

--------------------


If it is possible to create a situation where I can simply add a
lettercombination and not having to create such code for every new
lettercombination this would be great

Hope this is a help for you

Thanks
Hein

 

[Zack Barresse]

Trust me, go with the conditional formatting like I showed you, you'll never
match it with all the VBA in the world, it'll just slow you down.

 

[Hein]

Actually Zack I've figured it out
After fiddling with some code and changing "=" to "Like" it works

What I haven't figured out yet is how to creat a "basket" somewhere in
the top lines of the code where I can simply add the new text

Hein

PS
The code:


Code:
--------------------

Dim C as Range

For each C in selection.cells
if C like "DQ" or C like "*DQ*" then
C.interior.colorindex = 6
C.interior.pattern = xlSolid
end if
next C
end sub

 

[L. Howard Kittle]

Maybe something like this. Then just change F1 & F2.

NOT tested.

Dim C As Range
Dim i As String
Dim j As String

i = Range("F1").Value
j = Range("F2").Value

For Each C In Selection.Cells
If C Like i Or C Like j Then
C.Interior.ColorIndex = 6
C.Interior.Pattern = xlSolid
End If
Next C

HTH
Regards,
Howard

 

[Zack Barresse]

I'll go back to the solution I proposed first, with that, if you name the
entire column, you can just keep adding to your hearts content. This way,
you can now change 3 conditions into 1 that spans 65536 options.