SOLVED

Conditional formatting based on 2 columns of text.

%3CLINGO-SUB%20id%3D%22lingo-sub-1582641%22%20slang%3D%22en-US%22%3EConditional%20formatting%20based%20on%202%20columns%20of%20text.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582641%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20alternate%20the%20fill%20color%20as%20below%20without%20having%20to%20do%20it%20manually.%20I%20will%20often%20have%20thousands%20of%20lines%20and%20this%20can%20be%20tedious.%20Every%20time%20either%20the%20%22Stop%20Name%22%20or%20%22Line%22%20changes%2C%20I%20want%20it%20to%20change%20to%20either%20no%20fill%20or%20blue.%20The%20number%20of%20lines%20varies%20between%201%20and%204.%20Any%20suggestions%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Robert1967_0-1597174178742.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211855i133603A4C9390586%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Robert1967_0-1597174178742.png%22%20alt%3D%22Robert1967_0-1597174178742.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ERobert%20Gauthier%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1582641%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582798%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%202%20columns%20of%20text.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582798%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F756359%22%20target%3D%22_blank%22%3E%40Robert1967%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20range%20you%20want%20to%20format%20conditionally.%3C%2FP%3E%0A%3CP%3EIn%20the%20following%2C%20I'll%20assume%20that%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20range%20begins%20in%20row%203.%3C%2FLI%3E%0A%3CLI%3EThe%20active%20cell%20in%20the%20selection%20is%20in%20row%203.%3C%2FLI%3E%0A%3CLI%3EThe%20Stop%20Name%20and%20Line%20columns%20are%20column%20A%20and%20B%2C%20respectively.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EOn%20the%20Home%20tab%20of%20the%20ribbon.%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FP%3E%0A%3CP%3ESelect%20'Use%20a%20formula%20to%20determine%20which%20cells%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(%24A%243%3A%24A3%26lt%3B%26gt%3B%24A%242%3A%24A2%2C1%2C0)%2BIF(%24B%243%3A%24B3%26lt%3B%26gt%3B%24B%242%3A%24B2%2C1%2C0)%26gt%3B0))%2C2)%3D0%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%20the%20light%20blue%20fill%20color.%3C%2FP%3E%0A%3CP%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWarning%3A%20it%20might%20be%20slow.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582947%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%202%20columns%20of%20text.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582947%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%3EHi%20I%20tried%20out%20the%20solution%20you%20came%20up%20with%20but%20was%20unable%20to%20make%20it%20work...%20are%20you%20sure%20about%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1583721%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%202%20columns%20of%20text.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1583721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F740158%22%20target%3D%22_blank%22%3E%40Poogermum%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20uploaded%20a%20small%20sample%20workbook%3A%20see%20%3CA%20href%3D%22https%3A%2F%2Fwww.dropbox.com%2Fs%2Fmddvsbdy2gjbmtm%2FCF2Columns.xlsx%3Fdl%3D1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.dropbox.com%2Fs%2Fmddvsbdy2gjbmtm%2FCF2Columns.xlsx%3Fdl%3D1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S3460.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211998i352CCDF2F6A45ED6%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22S3460.png%22%20alt%3D%22S3460.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584175%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%202%20columns%20of%20text.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584175%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%3EWOW!%20Thank%20you%20very%20much.%20Works%20perfectly.%3C%2FP%3E%3CP%3EAwesome%20!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

Robert1967_0-1597174178742.png


Robert Gauthier

4 Replies
Highlighted
Best Response confirmed by Robert1967 (New Contributor)
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.

Highlighted

@Hans Vogelaar 

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

Highlighted
Highlighted

@Hans Vogelaar 

WOW! Thank you very much. Works perfectly.

Awesome !!