Searching within coloumns

[robert_woodie]

I need some advice or example code on how to search within a coloumn.

I have one coloumn of keywords seperated by commas. each row has
corrisponding file. I want to write a macro which starts with a box t
input search term and then goes away and searchs the coloumn for tha
word, if possible a find next function aswell. When it finds the wor
it should highlight the row.

Thanks in advance
Rober

 

[David McRitchie]

Hi Robert,
Sounds like something you could do with Conditional Formatting
If it is in column A that you want to test you could put a value to
be tested in cell A1 and use the following:

select column A (these are the cells that would be colored)
format, conditional formatting
condition 1: formulas is: =AND($A$1 = $A1,ROW()>1,$A$1<>"")
format button: pattern, choose a light pastel color

If instead of a single cell you want to color entire rows then
use Ctrl+A at the beginning instead of selecting just column A

The formula is based on the active cell when entering the formula
so cell A1 should be the active cell. The coloring is based on
which cells were selected prior to entering a conditional formula.
There is a limit of 3 conditional formatting per cell. If you selected
all cells so you can color each row then each cell on worksheet
has used up 1 of those allowed 3 C.F. per cell. But for what you
asked the limit would not be a problem as you have only 1 test.

More on Conditional Formatting in
http://www.mvps.org/dmcritchie/excel/condfmt.htm

An alternative that might be of interest is Filtering

 

[robert_woodie]

ok this works great (nearly). however i dont understand it so dont kno
how to modify it!! I would like it to highlight the row if what is i
A1 is anywhere within the cell as i have multiple words seperated wit
commas. At the moment it only works if the cell is exactlly equal to

thanks
rober

 

[David McRitchie]

Hi Robert,
Make the same cell selection as before
This time for Conditional Formatting use:
=AND(FIND($A$1,$A1),ROW()>1,$A$1<>"")

The main part of the formula is FIND($A$1,$A1)
so on any cell on any row the actual comparison
will be that the cell in column A is compared to cell A1
If the condition is true the conditional formatting (color)
takes place. The AND is used so that it will not
affect color on row 1 and that no checking is done
if cell $A$1 is empty.

// David

 

[robert_woodie]

thats cracked it. Thanks very much for your help david.

Robert

 

[David McRitchie]

Hi Robert, (correction to use SEARCH instead of FIND)
Make the same cell selection as before
This time for Conditional Formatting use:
=AND(SEARCH($A$1,$A1),ROW()>1,$A$1<>"")

SEARCH Worksheet Function is not case-sensitive and
allows wildcards.

For an exact match to a wildcard character of ?, or *, or ~
precede with a ~ (tilde).

FIND Worksheet Function (case sensitive) does not allow wildcards.

Wildcards: ? single character, * any number of characters.

Find (Ctrl+F) and Replace (Ctrl+H) shortcuts are not case
sensitive and allow wildcards and overrides of wildcards.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm