trying to conditional format based on words in a range.

**bopsgtir** · 10-31-2010, 01:14 PM

Sorry the title may not make complete sense, im not very goot at explaining this.

Basically im sent 200 addresses a day on an excel sheet, what i want to do is put that information onto my master sheet and for it to highlight an cells that have key words in them, like blocks of flats that i do not want to attend.

an example would be, say i had four jobs roads or blocks that i didnt want to go to id put them in a master list like below then i would drop my work below and if it said missenden in any cell it would then highlight. does that make any sense to anyone.

a
1 missenden
2 high level drive
3 metrol central hights
4 hopton street

**martindwilson** · 10-31-2010, 02:48 PM

with your list in a1:a4 say
you can apply a conditional format

say your first address is in a11 then select starting from a11 all the other cells you want checked
then conditional format
use the use a formula option and put in this
=LOOKUP(2^99,SEARCH($A$2:$A$5,A11))>0
choose a colour.

you can either apply this after you enter the data
or pre do it making sure when you copy new stuff in you use paste special values it depends on the source of your data.
however a more robust solution would be with some vba code which would automate the process

**bopsgtir** · 11-02-2010, 11:42 AM

yes thats definatly what im trying to get at, the idea is a rolling report everyday i will put a 100 or so addresses into it and any address that i dont like goes into my conditional format list then if that address pops up it gets flagged, but the idea is that say the cell was flat 102 missenden se17, it would flag even though the cell is not an exact but because the word missenden appears. so a vba code would be a better way then.

**martindwilson** · 11-02-2010, 12:06 PM

flat 102 missenden se17
would be flagged with that formula. the string just has to contain any of the keywords in your list a1:a4
perhaps i should post an example

**bopsgtir** · 11-02-2010, 12:10 PM

ok thank you for the example thats exactly what im after, in that formula what is the 2^99 telling the computer to do

**martindwilson** · 11-02-2010, 12:34 PM

its just a resonably big number that wont be in the result
the look up looks for the nearest number to it which is the position of the text found in its respective cell eg for a11
it sort of ends up doing this
lookup(2^99,{10,#value!;#value!;#value})so it returns 10 which is the position of the first character of the string missenden in the string flat 102 missenden se17,
so it means it found it.

**bopsgtir** · 11-02-2010, 12:54 PM

ok excellent thank you for the help, so my next step then would be a vba code for this

**martindwilson** · 11-02-2010, 04:27 PM

vba would do it differently, i'm not to hot on that but it would be better as you wouldn't have to worry about overwriting formats

**bopsgtir** · 11-09-2010, 10:34 AM

i found an issue with the formula, if there is a blank cell in my key words list then every cell will highlight, sometimes there will be blank cells as this is an evolving list and i dont want to do the formatting everytime id rather say all every word in the list will be used and if there are blanks dont use. if that makes sense

**martindwilson** · 11-09-2010, 12:22 PM

do you mean in the middle of you list or at the end?

**bopsgtir** · 11-09-2010, 01:21 PM

if would be at the end, what i wanted was to use aq formula like
=lookup(2^99,search($a:$a))>0 then evertime i add something into column a it will then hightlight those aswell

**bopsgtir** · 11-09-2010, 01:22 PM

also is it possible to use a reference from a different worksheet for a conditional format, as in my keywords list being on sheet 2

**martindwilson** · 11-09-2010, 07:14 PM

i think a dynamic named range would work. let me or someone else work on that
ok here's a dynamic named range for use in cf
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A$2:$A$200),1)
(so list is now on sheet 2)
how to do it is shown here
hhttp://support.microsoft.com/kb/830287
so instead of =LOOKUP(2^99,SEARCH(a1:a4,A11))>0
use
=LOOKUP(2^99,SEARCH(check,A11))>0 where "check" is the name you define