Forum Discussion

grassland7964's avatar
grassland7964
Copper Contributor
Nov 19, 2023

Formatting won / loss records

Hi there.  I'm looking to format won / loss records in Excel.  For instance, if column D2 is 64-59 (a winning record), I'd like to color it green.  If column D2 = 36-48, I'd like to color it red.  Column D is formatted as text.  If wins are twice as many or more than losses (e.g. D2 = 99-31), I'd like to color it purple.  Thanks in advance for any help you can provide.  

Excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 19, 2023

    grassland7964 

    Let's say you want to apply this to D2:D100.

    Select this range. D2 should be the active cell in the selection.

     

    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

     

    =--LEFT(D2,FIND("-",D2)-1)<--MID(D2,FIND("-",D2)+1,10)

     

    Click Format...
    Activate the Fill tab.
    Select red as color.
    Click OK, then click OK again.

     

    Repeat these steps, but with the formula

     

    =--LEFT(D2,FIND("-",D2)-1)>--MID(D2,FIND("-",D2)+1,10)

     

    and green as color.

     

    Finally, repeat them again with the formula

     

    =--LEFT(D2,FIND("-",D2)-1)>=2*MID(D2,FIND("-",D2)+1,10)

     

    and purple as color.

     

    Please note that draws will not be colored.

Resources