![]() |
Change color of rows every time value in first column changes
Hi,
I am using Excel 2003. I have a spreadsheet with one-to-many relationship data. First column contains plot numbers that repeat if more than one species of plants were found at that plot, i.e. plot number can be repeated an unspecified number of times. I am trying perform a function that is similar to highlighting every other row, except I would like to highlight every other plot. Thanks! |
Answer: Change color of rows every time value in first column changes
Sure, I can help you with that! Here's how you can change the color of rows every time the value in the first column changes:
What this formula does is it sums up all the values in column A from the first row to the current row, and then takes the remainder when divided by 2. If the remainder is 1, it means that the sum of all the values up to that row is odd, which indicates a new plot number. We use the MOD function to get the remainder, and the SUM function with a mixed reference ($A$1:A1) to sum up the values up to the current row. Now, every time the plot number changes, the row will be highlighted with the color you chose. |
Change color of rows every time value in first column changes
Example of layout
-- Don Guillett Microsoft MVP Excel SalesAid Software "Natasha" wrote in message ... Hi, I am using Excel 2003. I have a spreadsheet with one-to-many relationship data. First column contains plot numbers that repeat if more than one species of plants were found at that plot, i.e. plot number can be repeated an unspecified number of times. I am trying perform a function that is similar to highlighting every other row, except I would like to highlight every other plot. Thanks! |
Change color of rows every time value in first column changes
Is this what you want:
1 = shade 1 = shade 2 3 = shade 3 = shade 3 = sahde 4 4 5 = shade 6 6 7 = shade 7 = shade If so, assuming your data starts is in the range A2:A20 Select the range A2:A20 Goto the menu formatCondtional Formatting Select the Formula Is option Enter this formula in the box on the right: =MOD(ROUND(SUMPRODUCT((A$2:A2<"")/COUNTIF(A$2:A2,A$2:A2)),0),2) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "Natasha" wrote in message ... Hi, I am using Excel 2003. I have a spreadsheet with one-to-many relationship data. First column contains plot numbers that repeat if more than one species of plants were found at that plot, i.e. plot number can be repeated an unspecified number of times. I am trying perform a function that is similar to highlighting every other row, except I would like to highlight every other plot. Thanks! |
[quote=T. Valko;727214]Is this what you want:
1 = shade 1 = shade 2 3 = shade 3 = shade 3 = sahde 4 4 5 = shade 6 6 7 = shade 7 = shade If so, assuming your data starts is in the range A2:A20 Select the range A2:A20 Goto the menu formatCondtional Formatting Select the Formula Is option Enter this formula in the box on the right: =MOD(ROUND(SUMPRODUCT((A$2:A2<"")/COUNTIF(A$2:A2,A$2:A2)),0),2) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP __________________________________________________ __________________________________________________ _____ This equation works great, however, it only highlights the values from A2:A20. Does anyone know how the equation could be tweaked to highlight the entire row? Thanks! |
I found your forumla on here and it was very helpful. I'm doing the same thing and used your formula exactly as you posted it on here. the only problem is my data is 368,789 rows and excel freezes when I use this formula on such a large document. The forumula works properly however when I try to do anything in the document it freezes up. What I want to do is:
After the formula is done, filter column A based on color. Then I can select all visible cells from B right and manually change those colors at once. After that I can remove the formula on column A so I won't have to worry about slow-downs anymore. Any ideas on how to get this formula to work all the way down to the last row? Thanks so much for your help Quote:
|
All times are GMT +1. The time now is 06:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com