SOLVED

How to alternate table row colour based on the contents of a cell within the row

Copper Contributor

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...

 

CALOM1_0-1609502127578.png

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!

4 Replies

@CALOM1 

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.

@Hans Vogelaar 

 

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...

 

CALOM1_0-1609519154796.png

 

So how would I get the row colour to change when the first cell of the row changes from London to Paris etc.?

 

Thanks!

best response confirmed by CALOM1 (Copper Contributor)
Solution

@CALOM1 

Use the following formula in the conditional formatting rule:

 

=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)

1 best response

Accepted Solutions
best response confirmed by CALOM1 (Copper Contributor)
Solution

@CALOM1 

Use the following formula in the conditional formatting rule:

 

=MOD(ROUND(SUM(1/COUNTIF($A$2:$A2,$A$2:$A2)),0),2)

View solution in original post