changing cell color if cell has text
 

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

--
Hei
-----------------------------------------------------------------------
Hein's Profile: http://www.excelforum.com/member.php...fo&userid=2474
View this thread: http://www.excelforum.com/showthread.php?threadid=53238

changing cell color if cell has text
 

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

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM



"Hein" wrote in message
...

Hi
In parts of a large file I have to look for cells that have text in
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 trick
(and is limited to 3 conditions) and the search function from this site
doesn't seem to work?

In some cases there is only text in the cell, in other cases there will
be numbers, as well as text
I have to do the search in a lot of files and I presume that the list
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
Hein


--
Hein
------------------------------------------------------------------------
Hein's Profile:
http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=532384

changing cell color if cell has text
 

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" wrote:


Hi
In parts of a large file I have to look for cells that have text in
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 trick
(and is limited to 3 conditions) and the search function from this site
doesn't seem to work?

In some cases there is only text in the cell, in other cases there will
be numbers, as well as text
I have to do the search in a lot of files and I presume that the list
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
Hein


--
Hein
------------------------------------------------------------------------
Hein's Profile: http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=532384

changing cell color if cell has text
 

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


--
Hein
------------------------------------------------------------------------
Hein's Profile: http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=532384

changing cell color if cell has text
 

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.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Hein" wrote in message
...

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


--
Hein
------------------------------------------------------------------------
Hein's Profile:
http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=532384

changing cell color if cell has text
 

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

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


--
Hein
------------------------------------------------------------------------
Hein's Profile: http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=532384

changing cell color if cell has text
 

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

"Hein" wrote in message
...

Hi
In parts of a large file I have to look for cells that have text in
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 trick
(and is limited to 3 conditions) and the search function from this site
doesn't seem to work?

In some cases there is only text in the cell, in other cases there will
be numbers, as well as text
I have to do the search in a lot of files and I presume that the list
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
Hein


--
Hein
------------------------------------------------------------------------
Hein's Profile:
http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=532384

changing cell color if cell has text
 

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.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Hein" wrote in message
...

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

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


--
Hein
------------------------------------------------------------------------
Hein's Profile:
http://www.excelforum.com/member.php...o&userid=24744
View this thread: http://www.excelforum.com/showthread...hreadid=532384