comparing

Donna · External Since: May 01, 2009 Posts: 5

If you could help with this I would appreciate it.
I have 2 columns with initials.
Col A: will have initials
example Row 1 dcl, row 2 dcl, row 3 jah, row 4 peb, row 5, peb
Col B: is a constant list of possible initials
dcl, row, peb
I want to highlight in col A the initials if they are not lised in the
constant list in col b.
In the example I would want the cell in col a row 3 "jah" to be highlighed
since it was not listed in col b.
Thanks
Donna

Jacob Skaria · External Since: Mar 04, 2009 Posts: 441

1. Select the cell/Range (say A1:A10). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(A1<>"",COUNTIF(B:B,A1)=0)

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria

"Donna" wrote:

> If you could help with this I would appreciate it.
> I have 2 columns with initials.
> Col A: will have initials
> example Row 1 dcl, row 2 dcl, row 3 jah, row 4 peb, row 5, peb
> Col B: is a constant list of possible initials
> dcl, row, peb
> I want to highlight in col A the initials if they are not lised in the
> constant list in col b.
> In the example I would want the cell in col a row 3 "jah" to be highlighed
> since it was not listed in col b.
> Thanks
> Donna

Donna · External Since: May 01, 2009 Posts: 5

Yes, that worked great. Thank you Donna

"Jacob Skaria" wrote:

> 1. Select the cell/Range (say A1:A10). Please note that the cell reference A1
> mentioned in the formula is the active cell in the selection. Active cell
> will have a white background even after selection
>
> 2. From menu Format>Conditional Formatting>
>
> 3. For Condition1>Select 'Formula Is' and enter the below formula
> =AND(A1<>"",COUNTIF(B:B,A1)=0)
>
> 4. Click Format Button>Pattern and select your color (say Red)
> 5. Hit OK
>
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Donna" wrote:
>
> > If you could help with this I would appreciate it.
> > I have 2 columns with initials.
> > Col A: will have initials
> > example Row 1 dcl, row 2 dcl, row 3 jah, row 4 peb, row 5, peb
> > Col B: is a constant list of possible initials
> > dcl, row, peb
> > I want to highlight in col A the initials if they are not lised in the
> > constant list in col b.
> > In the example I would want the cell in col a row 3 "jah" to be highlighed
> > since it was not listed in col b.
> > Thanks
> > Donna

Ron Rosenfeld · External Since: Aug 26, 2003 Posts: 2348

On Mon, 2 Nov 2009 15:46:08 -0800, Donna <donna.DeleteThis@yahoo.com> wrote:

>If you could help with this I would appreciate it.
>I have 2 columns with initials.
>Col A: will have initials
>example Row 1 dcl, row 2 dcl, row 3 jah, row 4 peb, row 5, peb
>Col B: is a constant list of possible initials
>dcl, row, peb
>I want to highlight in col A the initials if they are not lised in the
>constant list in col b.
>In the example I would want the cell in col a row 3 "jah" to be highlighed
>since it was not listed in col b.
>Thanks
>Donna

Select A1
Conditional Format using the formula
=ISNA(MATCH(A1,$B$1:$B$3,0))

Format how you want it to look. E.g. Red fill with white font colors
stands out

Use format painter to copy format to the rest of the cells.

How you get into conditional formatting differs from Excel 2007 to previous.

In Excel 2007, you select Conditional formatting from the ribbon, then "New
Rule"; then "use a formula to determine which cells to format".

I don't recall exactly how to access this in the earlier versions.
--ron