Conditional Formatting Question

[Stephen Hartman]

Hi all,

I have a spread sheet with 9 columns and an infinite possibility of rows
(however it currently has 107 rows). What I would like to do is have each
cell in column b check the row above and below and if it finds the value in
either of the corresponding rows are equal then both rows that have the same
value in column b are highlighted the same background color. I have tried a
couple of ways in conditional formatting but can't get the coloring to work.
Any help is appreciated.

T.I.A.

 

[T. Valko]

Can you post a small sample to show us what you mean?

Is your first row of data row 1? If so, there is no row above row 1.

Biff

 

[Stephen Hartman]

A B C D E
F G H
Name Number Quantity Total Siganture Table Cost
Item #
Bob Jones 1234 5 Adult 21.50 31
2.50 A540
Tom Smith 5463 4 Adult 19.90 22
2.30 A540
Tom Smith 5463 5 Child 22.50 22
2.45 A541

What I am trying to do is check the number in column b with the row above
and below. If the numbers in column b match, I want the two rows with the
matching numbers in b to be the same background color. In the example
above, row 2 "bob jones" background color could be white but rows 3 and 4
where "tom smith's" numbers match should be the same color say yellow.

 

[David McRitchie]

Hi Stephen,
See http://www.mvps.org/dmcritchie/excel/condfmt.htm#grouping
Notice that you will need a helper column.

But if you simply want to identify rows where Column B value
also exists somewhere else in Column B then that is much
easier and no helper column is needed, but if you had
1,3,5,9,14,9,5,10 then all the 9's and 5' would have the
same color and are adjacent. You could two colors one
to indicate the first occurence of the number which is duplicated,
and another color to indicate it is a duplicate but not the
first occurence.

 

[az-willie]

A B C D E
F G H
Name Number Quantity Total Siganture Table Cost
Item #
Bob Jones 1234 5 Adult 21.50 31
2.50 A540
Tom Smith 5463 4 Adult 19.90 22
2.30 A540
Tom Smith 5463 5 Child 22.50 22
2.45 A541

What I am trying to do is check the number in column b with the row above
and below. If the numbers in column b match, I want the two rows with the
matching numbers in b to be the same background color. In the example
above, row 2 "bob jones" background color could be white but rows 3 and 4
where "tom smith's" numbers match should be the same color say yellow.

============================
I think you would use two rules. If B2=B3 then color=yellow. If B4=b3
then color =yellow. These two rules are applied to row 3 then copies to
each row if you want to maintain the system throughout the sheet.

I use a similar set of rules for stocks. If a stock makes a profit the
row is colored green. If it loses the row is colored red. Used two rules
to do that. It might be possible to do it with one rule, but I'm not
smart enough to know how. Just learned how to do this a week or so ago.