Forum Discussion
Conditional formatting based on 2 columns of text.
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
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.
4 Replies
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.
Hi I tried out the solution you came up with but was unable to make it work... are you sure about it?
I have uploaded a small sample workbook: see https://www.dropbox.com/s/mddvsbdy2gjbmtm/CF2Columns.xlsx?dl=1
