Forum Discussion

render_settings's avatar
render_settings
Copper Contributor
Jul 22, 2023
Solved

Conditional formatting

Hello,

 

Basically, I am trying to color code my 2 columns based on text rules but I'm wondering if there was a function/formula for what I'm trying to accomplish. Both columns will have True or False and if a row contains both True in each column, I want to color both green. If the row contains both False, then I want to color it both red. If the row contains True then False or vice-versa, then I want to color both cells yellow. What is the easiest way to do this? Thank you!

  • render_settings 

    Let's say the values in C2:D100.

    Select this range. The active cell in the selection should be in the top row, i.e. C2 or D2.

     

    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($C2=TRUE, $D2=TRUE)

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

     

    Repeat these steps, but with the formula

    =AND($C2=FALSE, $D2=FALSE)

    and red.

     

    Finally, repeat them again with the formula

    =OR(AND($C2=TRUE, $D2=FALSE), AND($C2=FALSE, $D2=TRUE))

    and yellow.

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        render_settings 

        Thanks for your PM.

        Try these formulas:

         

        =COUNTIF($F2:$G2,"*put*")=2

         

        =COUNTIF($F2:$G2,"*call*")=2

         

        =AND(COUNTIF($F2:$G2,"*put*")=1,COUNTIF($F2:$G2,"*call*")=1)

Resources