I have two colunms of numbers in excel, what i wanted to do is highlight the numbers that i the same on the sheet, instead of looking through the sheet, is this possible. Thanks

You can use Conditional Formatting to get what you want. If your data is in Column A and Column B:

Conditional Formatting 2007

First Column A:

1) Select your range of cells: A1 thru A14

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF(ISERROR(MATCH(A1,$B$1:$B$14,0)),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select your first pretty color, IE Red

9) Click OK

10) Click OK

Now for Column B

1) Select your range of cells: B1 thru B14

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =IF(ISERROR(MATCH(B1,$A$1:$A$14,0)),TRUE,FALSE)

6) Click on the Format button

7) Select the Fill Tab

8) Select your next pretty color, IE Green

9) Click OK

10) Click OK

The cells in column A that have a match in column B will turn Red

The cells in column B that have a match in column A will turn GreenThe above are set for only 14 rows, change to suit your needs.

MIKE

Here's a fun way...and you'll learn multiple things all at once. Read the Excel Help files on Conditional Formatting.

Then, follow this process for Excel 2003. The concept is the same for 2007/2010, but the steps are slightly different.

e.g You want to compare A1:A5 with B1:B5 and highlight any matching values.

Select A1:A5

Choose Format...Conditional Formatting...Formula Is:

=NOT(ISERROR(MATCH(A1,$B$1:$B$5,0)))

Choose a Fill pattern.

Since you selected A1:A5, the Conditional Formatting will be applied to each cell individually. Whenever the formula returns TRUE for a given cell, that cell will be formatted with the format you chose.

How it works:

The MATCH function will try to match e.g. A1 with the values in B1:B5. If it doesn't find it, it will return an error.

The ISERROR function will check to see if the MATCH function returned an error. If the MATCH function finds the value, the ISERROR will return FALSE since it's not an error.

The NOT function will "negate" the FALSE and make it TRUE.

In other words, if MATCH doesn't return an error, then the value must be in the list and the formula will return TRUE. Therefore your cell will be highlighted.

Select B1:B5 and repeat the process, "reversing" the formula.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Mike, We've talked about this before. It's that habit thing, remember? ;-)

The use of IF functions in CF usually means more keystrokes. I'm getting old and lazy and only have a limited number of keystrokes left.

Yours:

=IF(ISERROR(MATCH(B1,$A$1:$A$14,0)),TRUE,FALSE)

Mine:

=NOT(ISERROR(MATCH(A1,$B$1:$B$5,0)))

That's 11 keystrokes I can use somewhere else. ;-)

Hey wait..I can save an additional 12 keystrokes if I use this:

=MATCH(A1,$B$1:$B$5,0)>0

That'll also return TRUE if a match is found.

(Just having fun!)

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

markwebb Use DerbyDad03's formula, I gave you formulas for finding a

Uniquenumber in a list, IE.If the number is in Column A

but notin Column B it will turn Red

If the number is in Column Bbut notin Column A it will turn GreenSorry.

MIKE

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History