Forum Discussion
Solved
Highlighting and grouping rows that share a value
I have a very large report that is distributed daily that requires thousands of rows of data that need to be reviewed. I'm trying to make it easier for the user to read the rows in groups by highlighting the alternate groups. Similar to when you use the Format as Table feature and every other row alternates color. But in this instance, I want to group a number of rows together in the same color based on a shared value and alternate the color for each grouped set of rows. Any idea on how to manage this and what conditional formatting formulas or other features I could use to accomplish this?
Example to keep it simple: if I wanted to alternate rows between different Objects in the below so that it would be grey for Apple, white for Orange and grey again for Watermelon, how would I do this:
| Aisle | Object | Attribute |
| 1 | Apple | Red |
| 1 | Apple | Round |
| 1 | Apple | Small |
| 1 | Orange | Orange |
| 1 | Orange | Round |
| 1 | Orange | Small |
| 2 | Watermelon | Green |
| 2 | Watermelon | Large |
You may use formula like
=INDEX($K$2:$K$40,MATCH(1,INDEX(($D2>=$I$2:$I$40)*($D2<=$J$2:$J$40),0),0))
Please see attached
2 Replies
Select the rows starting from row 2 down. The active cell in the selection should be in row 2.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=ISODD(COUNTA(UNIQUE($B$2:$B2)))
Click Format...
Activate the Fill tab.
Select grey as fill color.
Click OK, then click OK again.HansVogelaar Thank you this worked!
