Solved EXCEL 2007: conditional formatting.

September 19, 2011 at 08:08:35
Specs: Windows XP
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?


See More: EXCEL 2007: conditional formatting.

Report •

✔ Best Answer
September 20, 2011 at 17:16:21
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 red since 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.



#1
September 19, 2011 at 14:35:11

Report •

#2
September 19, 2011 at 17:03:25
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.


Report •

#3
September 20, 2011 at 08:11:40
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    2 

Please 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    25 

What 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?


Report •

Related Solutions

#4
September 20, 2011 at 17:16:21
✔ 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 red since 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.


Report •

#5
September 21, 2011 at 04:19:20
DerbyDad03,

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


Report •

#6
September 21, 2011 at 04:21:09
DerbyDad03,

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

Thank you!

edited by moderator: email address deleted


Report •

#7
September 21, 2011 at 04:45:17
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.


Report •

Ask Question