Forum Discussion
How to alternate table row colour based on the contents of a cell within the row
Hi,
I am wanting to add in conditional formatting to my table so that the row colour alternates when the contents of one of the row's cells changes...
E.g. How do I get the row colour to alternate when it changes from Oct-20 to Nov-20 and from Nov-20 to Dec-20 and so on?
The aim of this is so it is easier to see what falls into each month. Data gets added to this table all the time and I don't want to create conditional formatting rules each time a new month gets added. I also wanted to use this on another table which does not use months and instead I wanted the rows to be grouped by rows with the same random text in a cell, and this comes up a lot more regularly than monthly so even more tedious!
I would prefer not to have to switch to a macro-enabled spreadsheet because I use Microsoft flow to input the data into the spreadsheet remotely so this would not work.
Can this be done? Thanks!
Use the following formula in the conditional formatting rule:
=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)
4 Replies
Let's say the Months are in A2 and down.
Select the entire data body of the table (i.e. the table without the header row.
I will assume that the active cell is in row 2.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cell to format'.
Enter the formula
=MOD(SUM(IF(FREQUENCY($A$2:$A2,$A$2:$A2),1)),2)
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.
As you add rows to the table, Excel will automatically apply the rule to the new rows too.
Thanks for your reply! This works great for dates but if I wanted the active bit to be data other than a date, such as text that changes...
So how would I get the row colour to change when the first cell of the row changes from London to Paris etc.?
Thanks!
Use the following formula in the conditional formatting rule:
=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)
