I have two columns of numbers. One column has 60 rows and the other has 500. All 60 of the values in column 1 can be found in column 2. I would like a formula for conditional formatting that will find those 60 values from column A in column B to highlight them in red to visually separate them from all the others values. Does such a formula exist?

Assuming your column of 60 is in A1:A60 and your column of 500 starts in B1, try this: Select B1:B500.

Use this formula:

=MATCH(B1,$A$1:$A$60,0)

(I don't have 2007, so I can't give you the step-by-steps, just the formula to use.)

Here's a step by step: 1) Select your cell B1:B500.

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 DerbyDads formula: =MATCH(B1,$A$1:$A$60,0)

6) Click on the Format button

7) Select the Fill Tab

8) Select a pretty color

9) Click OK

10) Click OK

MIKE

That worked! Thank you so much for helping me save hours of my time!

Ask Your Question

Weekly Poll