Auto Fill Cells if numbers match another column/range?

**michaelrusk** · 12-05-2012, 08:29 AM

Hey Guys,

I have a column full of numbers from going from L28 down to L159 on one worksheet.

On another worksheet I have a range of EMPTY cells from G9 to H500.

what I need, if a number is typed anywhere in the range G9:H500 which is already in my column, I would like the cell in the column to COLOR in.

Any ideas??

**benishiryo** · 12-05-2012, 08:34 AM

hi michaelrusk. you could do a simple conditional formatting. first, name the range in Sheet 1 L28:L159 (i'll call it "xRange"). then go to Sheet2 & highlight your list from G9:H500. it's important to start on G9 & highlight right & downwards. go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=COUNTIF(xRange,G9)>0

in 2010, i think they might have solved the problem of referencing another worksheet. so you can do this without naming the range.
=COUNTIF(Sheet2!A:A,A1)>0

**michaelrusk** · 12-05-2012, 09:44 AM

I keep getting a message saying about the worksheet criteria. Referencing another worksheet can't be done?

In your description, You say "name the range in Sheet 1 L28:L159" - but that is my LIST of numbers.
you then say "then go to Sheet2 & highlight your list from G9:H500" - but that is RANGE.

is this where im going wrong?

**michaelrusk** · 12-07-2012, 08:56 AM

Hey Guys/benishiryo

I have the above working!! (I now have the info all in the same worksheet so I'm no longer getting the message about "Referencing another worksheet"

However, I've came across a small snag! my spreadsheet is set up as follows...

Column M9 down to M40 is my list of numbers. These numbers turn ORANGE if an exact duplicate match is entered anywhere within cell range G9:I1000

this works perfectly!!

Now, sometimes I enter a number into my range more than once... is there anything I can do where If i enter a number once it turns ORANGE, entered twice it turns yellow, three times it turns blue??

The formula/rule I'm using already is...

Conditional formatting - =COUNTIF($G$9:$I$1000,M9)

**benishiryo** · 12-07-2012, 09:07 AM

i suggest you add something to your existing formula so that it will not get mixed with the rest.
=COUNTIF($G$9:$I$1000,M9)=1

then repeat the whole thing again but changing the formula slightly & choose a different colour
2 times:
=COUNTIF($G$9:$I$1000,M9)=2

3 times
=COUNTIF($G$9:$I$1000,M9)=3

i think Excel 2003 can only have 3 colours. any more than that, you'll have to use VBA. so your last condition may want to include the rest?
=COUNTIF($G$9:$I$1000,M9)>=3

upload a sample Excel file if you face any problems. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon