Exact Match colour

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

@Homeonthecouch - 11 Dec 2007 16:08 GMT

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.

Reply to this Message

Pete_UK - 11 Dec 2007 16:33 GMT

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

> Hello,
>
[quoted text clipped - 15 lines]
>
> Andrew.

Reply to this Message

@Homeonthecouch - 11 Dec 2007 18:27 GMT

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

On Dec 11, 4:08 pm, "@Homeonthecouch" <m...@home.com> wrote:

> Hello,
>
[quoted text clipped - 15 lines]
>
> Andrew.

Reply to this Message

Pete_UK - 11 Dec 2007 18:45 GMT

You're welcome, Andrew - thanks for feeding back.

Pete

> Works a treat !
>
[quoted text clipped - 40 lines]
>
> - Show quoted text -

Reply to this Message

Dave Peterson - 11 Dec 2007 16:35 GMT

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.

> Hello,
>
[quoted text clipped - 15 lines]
>
> Andrew.

Signature

Dave Peterson

Reply to this Message

Rate this thread: