highlight differences

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

Donna

Please help with final piece to my worksheet

I have :
Col A : I have a list of all the divisions downloaded for that day (there is
more than one line listed per division)
Col B I have a fixed list of certain divisions

What I would like is to highlight any div in Col A that was not listed in
my fixed Col B
Thanks Donna
 
Assuming the range of column A is A1:A20 and the range of column B is
B1:B10. Also assuming no empty cells within either range.

Select the *entire* range A1:A20 starting from cell A1. Cell A1 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula will be relative to the active cell.

Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=ISNA(MATCH(A1,B$1:B$10,0))
Click the Format button
Select the desired style(s)
OK out
 
1. Select ColumnA. 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 formula
=AND(A1<>"",COUNTIF(B:B,A1)=0)

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

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

If this post helps click Yes
 
Back
Top