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

@Grassland79 

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

 

=AND(B2="W",OR(A2="W",C2="W"))

 

Click Format...

Select green as font color.

Click OK, then click OK again.

 

Repeat the above steps, but with

 

=AND(B2="L",OR(A2="L",C2="L"))

 

and red as font color.

@Grassland79 

Rules could be as here

image.png

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!