 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2007Conditional Formatting Question
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. 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 > Hi all, > [quoted text clipped - 7 lines] > > T.I.A. 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. > Can you post a small sample to show us what you mean? > [quoted text clipped - 13 lines] >> >> T.I.A. 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. --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm > A B C D E > F G H [quoted text clipped - 30 lines] > >> > >> T.I.A. > A B C D E > F G H [quoted text clipped - 30 lines] >>> >>> T.I.A. ============================ 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.