Conditional formatting a row based on change in data

[paprikajo]

Hi trying to shade rows in groups based on when the data in column 1 changes.
ex.

col A B C
1john data data
2john data data
3jane data data
4 jim data data
5jim data data

Shade 1 & 2 with john, the whole row
no shade line 3
shade line 4,5 with jim, the whole row

I tried the formula =MOD(ROUND(SUMPRODUCT((A$2:A2<>"")/COUNTIF(A$2:A2,A$2:A2)),0),2)

This works great shading as the data changes, but it doesn't shade the whole row. Only column A. Can you help modify the formula to shade the whole row?

Thanks.

 

[T. Valko]

Hi trying to shade rows in groups based on when the data in column 1 changes.
ex.

col A B C
1john data data
2john data data
3jane data data
4 jim data data
5jim data data

Shade 1 & 2 with john, the whole row
no shade line 3
shade line 4,5 with jim, the whole row

I tried the formula =MOD(ROUND(SUMPRODUCT((A$2:A2<>"")/COUNTIF(A$2:A2,A$2:A2)),0),2)

This works great shading as the data changes, but it doesn't shade the whole row. Only column A. Can you help modify the formula to shade the whole row?

Thanks.

Select the entire range of cells that you want to format. Let's assume you want to format A2:C10.

Select the *entire* range A2:C10 starting from cell A2.
Cell A2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

And use this formula:

=MOD(ROUND(SUMPRODUCT(($A$2:$A2<>"")/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)

 

[k8stuff]

Thank you - this was exactly what I needed today!