Conditional Highlighting

[jayknitter]

I want to highlight entries in a table that occur in a seperate list.

My table looks something like this:


A B C D
E A F G
H D J K


And the "list" which is just a range of cells looks like this:

H
A
F


I'm using Excel 2007. I tried selecting both ranges that represent
the table and list and do conditional highlighting so that any
duplicate entries are highlighted except there is one problem with the
way that worked. Since my table has some duplicate entries itself it
highlighted "A" (as I intended, because it was in the table and list)
but highlighted "D" also (not as I intended, because it was in the
table twice).

Any hints as to how I might get what I want accomplished (Highlight
cells in the table that occur in the list)?

 

[Tieske]

Assumed that your table below has the "A" value in cell A4; you can format
the A4 cell with this formula;

=ISERROR(VLOOKUP(A4;CheckList;1;FALSE))
The matched items will get the formatting

=NOT(ISERROR(VLOOKUP(A4;CheckList;1;FALSE)))
The unmatched items will get the formatting

Where the CheckList range is the list H, A, F in your example below. After
that you can copy the format from A4 to the remainder of your table.

Basics working: The cell value is being looked-up in the "CheckList" range
for an exact match, if it isn't found it will return an error.

regards,
Tieske

 

[jayknitter]

Assumed that your table below has the "A" value in cell A4; you can format
the A4 cell with this formula;

=ISERROR(VLOOKUP(A4;CheckList;1;FALSE))
The matched items will get the formatting

=NOT(ISERROR(VLOOKUP(A4;CheckList;1;FALSE)))
The unmatched items will get the formatting

Where the CheckList range is the list H, A, F in your example below. After
that you can copy the format from A4 to the remainder of your table.

Basics working: The cell value is being looked-up in the "CheckList" range
for an exact match, if it isn't found it will return an error.

regards,
Tieske










- Show quoted text -

Ok, that formula works great! Thanks.

Now how do I copy just the conditional highlighting formula around to
the other cells in my table? When I use the Format Painter the cells
which are effected by the conditional highlighting change as expected,
but the formula itself doesn't change. For example, I set up the
formula for cell A4 (the formula is =ISERROR(VLOOKUP(A4;CheckList;
1;FALSE))) then copy the formatting to cell A5 but the formula stays
the same (still referencing cell A4). My tabe is too big to update
these formulas manually! Any more hints?

 

[Gord Dibben]

Check the formula in CF.

Might be that Excel helped you out...not......by placing $ signs around the cell
reference.

i.e. $A$4 instead of A4 which you should have.

It is easiest when doing this to select all the cells first then enter the
formula in CF rather than painting around after.


Gord Dibben MS Excel MVP

 

[jayknitter]

Check the formula in CF.

Might be that Excel helped you out...not......by placing $ signs around the cell
reference.

i.e. $A$4 instead of A4 which you should have.

It is easiest when doing this to select all the cells first then enter the
formula in CF rather than painting around after.

Gord Dibben MS Excel MVP




- Show quoted text -

Thanks for the tips, I had to get all my relative and absolute
references correctly setup and things worked great!

 

[jayknitter]

Assumed that your table below has the "A" value in cell A4; you can format
the A4 cell with this formula;

=ISERROR(VLOOKUP(A4;CheckList;1;FALSE))
The matched items will get the formatting

=NOT(ISERROR(VLOOKUP(A4;CheckList;1;FALSE)))
The unmatched items will get the formatting

Where the CheckList range is the list H, A, F in your example below. After
that you can copy the format from A4 to the remainder of your table.

Basics working: The cell value is being looked-up in the "CheckList" range
for an exact match, if it isn't found it will return an error.

regards,
Tieske










- Show quoted text -

Ok, now for a similar but different problem....

I have two tables, I want any value in table 1 to get highlighted that
appears in table 2. Again, table 1 may have duplicate values. Any
hints?

Thanks in advance!