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:

1. Select the entire range of your data, including the first column with plot numbers.
2. Click on the "Format" menu and select "Conditional Formatting".
3. In the "Conditional Formatting" dialog box, select "Formula Is" from the drop-down menu under "Condition 1".
4. In the formula bar, enter the following formula:
   Formula:

   ---

   =MOD(SUM($A$1:A1),2)=1 


   ---

5. Click on the "Format" button and choose the fill color you want to use for the highlighted rows.
6. Click "OK" to close the "Format Cells" dialog box.
7. Click "OK" again to close the "Conditional Formatting" dialog box.

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