Jan 01 2021 04:01 AM
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!
Jan 01 2021 06:46 AM
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.
Jan 01 2021 08:40 AM
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!
Jan 01 2021 09:10 AM
SolutionUse the following formula in the conditional formatting rule:
=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)
Jan 01 2021 10:21 AM
Jan 01 2021 09:10 AM
SolutionUse the following formula in the conditional formatting rule:
=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)