Re: Searching within coloumns
David McRitchie
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
---
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
"robert_woodie >" <<robert_woo...@excelforum-nospam.com> wrote in ...
> I need some advice or example code on how to search within a column.
>
> I have one coloumn of keywords separated by commas. each row has a
> corresponding file. I want to write a macro which starts with a box to
> input search term and then goes away and searchs the column for that
> word, if possible a find next function as well. When it finds the word
> it should highlight the row.
David McRitchie
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 >" <<robert_woo...@excelforum-nospam.com> wrote \
> ok this works great (nearly). however i dont understand it so dont know
> how to modify it!! I would like it to highlight the row if what is in
> A1 is anywhere within the cell as i have multiple words separated with
> commas. At the moment it only works if the cell is exactlly equal to
David McRitchie
Make the same cell selection as before
This time for Conditional Formatting use:
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
"David McRitchie" <dmcri...@msn.com> wrote in message news:OiJHfmmQ...@tk2msftngp13.phx.gbl...