comparing

  • Thread starter Thread starter Donna
  • Start date Start date
D

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
 
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
Click to expand...

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
 
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
 
Yes, that worked great. Thank you Donna

Jacob Skaria said:
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
Click to expand...
 
Back
Top