SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2025416%22%20slang%3D%22en-US%22%3EHow%20to%20alternate%20table%20row%20colour%20based%20on%20the%20contents%20of%20a%20cell%20within%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2025416%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20wanting%20to%20add%20in%20conditional%20formatting%20to%20my%20table%20so%20that%20the%20row%20colour%20alternates%20when%20the%20contents%20of%20one%20of%20the%20row's%20cells%20changes...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CALOM1_0-1609502127578.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243659i1CF9A068557C5547%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22CALOM1_0-1609502127578.png%22%20alt%3D%22CALOM1_0-1609502127578.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EE.g.%20How%20do%20I%20get%20the%20row%20colour%20to%20alternate%20when%20it%20changes%20from%20Oct-20%20to%20Nov-20%20and%20from%20Nov-20%20to%20Dec-20%20and%20so%20on%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20aim%20of%20this%20is%20so%20it%20is%20easier%20to%20see%20what%20falls%20into%20each%20month.%20Data%20gets%20added%20to%20this%20table%20all%20the%20time%20and%20I%20don't%20want%20to%20create%20conditional%20formatting%20rules%20each%20time%20a%20new%20month%20gets%20added.%20I%20also%20wanted%20to%20use%20this%20on%20another%20table%20which%20does%20not%20use%20months%20and%20instead%20I%20wanted%20the%20rows%20to%20be%20grouped%20by%20rows%20with%20the%20same%20random%20text%20in%20a%20cell%2C%20and%20this%20comes%20up%20a%20lot%20more%20regularly%20than%20monthly%20so%20even%20more%20tedious!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20prefer%20not%20to%20have%20to%20switch%20to%20a%20macro-enabled%20spreadsheet%20because%20I%20use%20Microsoft%20flow%20to%20input%20the%20data%20into%20the%20spreadsheet%20remotely%20so%20this%20would%20not%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20this%20be%20done%3F%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2025416%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2025721%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20alternate%20table%20row%20colour%20based%20on%20the%20contents%20of%20a%20cell%20within%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2025721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F915384%22%20target%3D%22_blank%22%3E%40CALOM1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20Months%20are%20in%20A2%20and%20down.%3C%2FP%3E%0A%3CP%3ESelect%20the%20entire%20data%20body%20of%20the%20table%20(i.e.%20the%20table%20without%20the%20header%20row.%3C%2FP%3E%0A%3CP%3EI%20will%20assume%20that%20the%20active%20cell%20is%20in%20row%202.%3C%2FP%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cell%20to%20format'.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMOD(SUM(IF(FREQUENCY(%24A%242%3A%24A2%2C%24A%242%3A%24A2)%2C1))%2C2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20Format...%3C%2FP%3E%0A%3CP%3EActivate%20the%20Fill%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20a%20highlight%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3EAs%20you%20add%20rows%20to%20the%20table%2C%20Excel%20will%20automatically%20apply%20the%20rule%20to%20the%20new%20rows%20too.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2025858%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20alternate%20table%20row%20colour%20based%20on%20the%20contents%20of%20a%20cell%20within%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2025858%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply!%20This%20works%20great%20for%20dates%20but%20if%20I%20wanted%20the%20active%20bit%20to%20be%20data%20other%20than%20a%20date%2C%20such%20as%20text%20that%20changes...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CALOM1_0-1609519154796.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243666iB51CA08D325A90FE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22CALOM1_0-1609519154796.png%22%20alt%3D%22CALOM1_0-1609519154796.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20how%20would%20I%20get%20the%20row%20colour%20to%20change%20when%20the%20first%20cell%20of%20the%20row%20changes%20from%20London%20to%20Paris%20etc.%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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)