Conditional Formatting Based on Contents of a List

[Raeven]

Basically, I have two lists of data in MS Excel. One is a list o
charge codes that are valid to be used by my department. The other i
a list of charge codes that employees actually used. How can
highlight the charge codes that appear in the second list that do no
appear in the first list of valid codes?

Thanks

 

[Frank Kabel]

Hi
if your first list is in A1:A100 and your second list in B1:B100 try
the following
- select B1:B100
- goto 'Format - Conditional Format'
- enter the formula
=COUNTIF($A$1:$A$100,B1)=0
- choose your format

 

[Bob Phillips]

Use Conditional Formatting.

If the first list is in say A1:A10, and the second is in H1:H20

select H1:H20
menu Format>Conditional Formatting
change condition 1 to Formula Is
add aformula of =COUNTIF($A$1:$A$10,H1)=0
select the pattern tab
choose a colour
OK out

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jason Morin]

Assuming the valid codes are in col. A and charge codes
actually used are in col. B:

1. Select the range of codes found in col. B.
2. Format > Conditional Formatting
3. Select "Formula Is" and insert:
=COUNTIF($A:$A,$B1)<1
4. Format as desired.

HTH
Jason
Atlanta, GA