Conditional formatting based on 2 columns of text.

[Robert1967]

I want to alternate the fill color as below without having to do it manually. I will often have thousands of lines and this can be tedious. Every time either the "Stop Name" or "Line" changes, I want it to change to either no fill or blue. The number of lines varies between 1 and 4. Any suggestions?

Robert Gauthier

[#2 · Accepted solution]

@Robert1967 

Select the range you want to format conditionally.

In the following, I'll assume that:

• The range begins in row 3.
• The active cell in the selection is in row 3.
• The Stop Name and Line columns are column A and B, respectively.

On the Home tab of the ribbon. select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=MOD(SUM(--(IF($A$3:$A3<>$A$2:$A2,1,0)+IF($B$3:$B3<>$B$2:$B2,1,0)>0)),2)=0

Click Format...

Activate the Fill tab.

Select the light blue fill color.

Click OK, then click OK again.

Warning: it might be slow.

[#3]

@Hans Vogelaar 

Hi I tried out the solution you came up with but was unable to make it work... are you sure about it?

[#4]

@Poogermum 

I have uploaded a small sample workbook: see https://www.dropbox.com/s/mddvsbdy2gjbmtm/CF2Columns.xlsx?dl=1 

 

[#5]

@Hans Vogelaar 

WOW! Thank you very much. Works perfectly.

Awesome !!