Search Macro With Cell Color Replacement

[tobiasescalante]

Ok the main goal here is to search for a list of names and wherever
those names apear in the cells have those cells change color to like
red that way it looks highlighted... my problem is there will be
multiple names so it would have to search for a list of names
individualy. they will not all apear in one cell.

I hope that made sence if you dont understand i can try to explain it
better...

 

[Frank Kabel]

Hi
you may use conditional formating: If your list of names is on a
different sheet (lets say sheet 1, range A1:A20) try the following
- select all cells on your second sheet for which you want to search
for the names (lets say this cell range starts in cell A1)
- goto 'Format - Conditional Format'
- enter the following formula
=COUNTIF('sheet1'!$A$1:$A$20,A1)>=1
- choose syour format (red color)

 

[tobiasescalante]

it doesnt work i cant call on another sheet and it only affects th
cells that are called anyways?

I have a huge list of names i want to highlight only a SET few that
would have to type out the names of ill have many many many sheets t
do this from so that wont work for me... would a module work? eh *
have no idea how to make one

 

[Frank Kabel]

Hi
not sure why this doesn't work for you?.
- Do you have a list with the specific names to highlight. If yes use
this range as the first parameter in the COUNTIF function
- after this select your TOTAL list of names and insert the following
formula in the conditional format dialog
=COUNTIF(range_with_set_names,upper_left_cell)
Replace the upper_left_cell with the upper left cell of your total name
list

Takes 10 seconds and works for huge lists of names?

If yu like, email me your spreadsheet and I'll set this up for you
frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany
"tobiasescalante >" <<[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[David McRitchie]

Hi Tobias,
Might help if you gave the exact text of any messages that you see
so that people can actually help you.i.e.
You may not use references to other worksheets or Workbooks for Conditional Formatting criteria.

In which case someone would immediately tell you to use a named
range, instead of an explicit range.a

=COUNTIF(myrange,$A1)>=1

I would make the cell being checked as $A1 then you
can color the entire row if you like.

select Sheet1!$A$1:$A$20
type myrange into the namebox to the left of the formula bar

use Insert, Name to make corrections or remove.

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

 

[tobiasescalante]

ok the names are on A1 - A3 im only testing this with 3 names for righ
now the list of names are from A2 - M27 Multiple names not all of the
are the ones in A1-A3 i only want it to change the color of the one
MATCHING A1-A3 im very sorry if im bad at explaining this... i wish
could explain it better lo

 

[Frank Kabel]

Hi
are these lists on separate sheets? But try the following (this should
work in all cases)
1. Step
- select the cells A1:A3
- goto 'Insert - Name - Define'
- Create A name for this range. E.g. call this name 'name_list'

2. Step
- select the cells A2:M27 (the entire range)
- goto 'Fromat - Conditional Format'
- choose Formula and enter the following formula
=COUNTIF(name_list,A2)>=1
- choose a format for this


--
Regards
Frank Kabel
Frankfurt, Germany
"tobiasescalante >" <<[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[tobiasescalante]

Awsome that works great thanks alot

 

[Frank Kabel]

Hi
thanks for the thanks.