Exact Match colour

  • Thread starter Thread starter @Homeonthecouch
  • Start date Start date
@

@Homeonthecouch

Hello,

I have a sheet that has numeric data and also alpha numeric data on it

I am using a lookup table to sort out the naming of the numeric's

e.g
12.12.12 Bill
14.14.14 Tom

I am wanting to know if I can colour a cell is not an exact match to the numbers I have defined with names in my
lookup table?

e.g
13.13.13, the cell will then look in my lookup range and turn the cell red to alert me.


As Always your help is appreciated.

Andrew.
 
Assume your table of numbers and names is in M1:N20, and you want this
to apply to the cell A1. Select the cell and click on Format |
Conditional formatting, then choose Formula Is in the first box and
enter this formula:

=ISNA(VLOOKUP(A1,$M$1:$M$20,1,0))

Click on the Format button, then the Patterns tab and choose your
(background) colour. Click OK twice. You can use the Format Painter
icon to apply that format to other cells.

Hope this helps.

Pete
 
Maybe you can use format|conditional formatting (xl2003 menu system) to show the
values that don't appear in the other list (if I understand correctly).

If the other list is on another worksheet, you'll have to give it (a single
column) a nice name. I used myList in my example.

Then I can use a formula like:

=iserror(match(a1,mylist,0))
or like this:
=countif(myList,A1)=0

to show the entries that don't appear in myList.
 
Works a treat !

Thank you very much

Andrew


Assume your table of numbers and names is in M1:N20, and you want this
to apply to the cell A1. Select the cell and click on Format |
Conditional formatting, then choose Formula Is in the first box and
enter this formula:

=ISNA(VLOOKUP(A1,$M$1:$M$20,1,0))

Click on the Format button, then the Patterns tab and choose your
(background) colour. Click OK twice. You can use the Format Painter
icon to apply that format to other cells.

Hope this helps.

Pete
 
Back
Top