comparing

[Donna]

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]

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

 

[Jacob Skaria]

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

 

[Donna]

Yes, that worked great. Thank you Donna

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