In Excel 2007, how could I change the format of a cell if the value (number) it contains appears in a selection in another sheet? For example: I've got a selections of cells in one spreadsheet "A": 1; 3; 11; 17; 18; 20; and, in another spreadsheet "B", I've got 1; 2; 3; 4; 5; 6; 7; 8; 9; 10. I'd like the cells on spreadsheet "B" which contain values that appear on cells in spreadsheet "A" (1 and 3) to be formatted with red font.

How could I do that?

✔ Best Answer

I understood your request the first time you posted it and I provided the solution. All you need to do is change the sheet name and ranges to match yours.

I just tested my solution with your example data using:

=COUNTIF(Insert!$A$1:$D$4,A1)>0

The following numbers turned

redsince they are in both tables:

1 2 3 4 5 6 7 8 9 10 13 19 23 24 25

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

Let's say the list of numbers in Sheet A is in A1:A6 Let's say the list of numbers in Sheet B is in A1:A10

Select B!A1:A10

Apply this Conditional Formatting rule to the selection:=COUNTIF(A!$A$1:$A$6,A1)>0

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

I apologize: maybe my problem was not posted in a clear manner. Let's try to make it again. I have two spreadsheets: the first one (called INPUT) has one group "A" (filled up with non-sequential data, random numbers from 1 through 25), example as follows:

A B C D 1 23 3 10 2 8 4 25 24 3 9 1 6 5 4 7 19 13 2Please note that the spreadsheet INPUT has only 15 numbers.

So far, ok. Then, in another spreadsheet called OUTPUT, I've got another group "B" of cells (five rows and five columns), which has a group of 25 cells with numbers from 1 through 25, exactly as follows:

A B C D E 1 1 2 3 4 5 2 6 7 8 9 10 3 11 12 13 14 15 4 16 17 18 19 20 5 21 22 23 24 25What I need is, when the sequence of numbers "A" changes, then the corresponding cells of the second group "B" would automatically change their colors (to RED) accordingly.

In the example above, INPUT's first number is "23" (cell A1) and "2" is its last number (D4); the corresponding cells in the OUTPUT spreadsheet (C5, "23" and B1, "2") should automatically change color to red, the same happening with the other 13 cells.

Is it clear now?

Could somebody give me a help?

I understood your request the first time you posted it and I provided the solution. All you need to do is change the sheet name and ranges to match yours.

I just tested my solution with your example data using:

=COUNTIF(Insert!$A$1:$D$4,A1)>0

The following numbers turned

redsince they are in both tables:

1 2 3 4 5 6 7 8 9 10 13 19 23 24 25

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

DerbyDad03, I'm sorry, but your solution didn't work to me. I'll go on looking for a solution, but thank you anyway!

DerbyDad03, Could you send your Excel worksheet to me? My e-mail address: xxx@.xxx.xxx

Thank you!

edited by moderator: email address deleted

Never post your email address in a public forum. We don't care if you end up getting a lot of spam, but we don't want to become known as a site where email addresses can be harvested. Once the bad guys start hanging around, we'll be subject to all sorts of security issues.

I have removed the email address from your post.

Tell what steps you performed to apply the Conditional Formatting and I'll see if I can tell where you went wrong.

Note that I used hard numbers in each worksheet. If you are using formulas, you'll need to tell me what formulas you are using. Maybe your numbers don't really match.

DerbyDad03

Office Forum Moderator

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

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History