Formatting Consecutive Wins and Losses

New Contributor

I am looking to change the font color of entries within a profit / loss spreadsheet if there are consecutive wins or losses.  I would like consecutive wins (W) to have green font and consecutive losses (L) to have red font.  For wins or losses that are not consecutive, I want black text  For instance. If I had the following:


L  L  W  L  W  W  L  L  W  L  L  W  W  W  W    in cells B2 – P2, I would want the first two L’s to have red text, the next W  L  to have black text, the following two W’s to have green text, the next two L’s to have red text, the W to have black text, the two L’s to have red text and the final four W’s to have green text.


Can this be done within Excel?  Any help would be appreciated.

4 Replies


Select B2:P2.


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

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

Enter the formula




Click Format...

Select green as font color.

Click OK, then click OK again.


Repeat the above steps, but with




and red as font color.


Rules could be as here


Thank you!
This worked except for part of the combination of WLWWWLLWLWLLLWL, specifically I2, J2, K2 cells which contain WLW. Those were colored Green, Red, Red instead of being black. Remainder of spreadsheet was fine. Any ideas? Thanks again for your help!