Forum Discussion

ebella212's avatar
ebella212
Copper Contributor
Sep 28, 2024

Conditional formatting on row based on text result from formula from another cell

I am trying to format the cells in a row between columns F-T based on the result of "Yes" from column U containing the IF formula =IF(J2=M2,"Yes","No").

 

 

When I apply the conditional formatting formula: =$U2="Yes" and applies to: =$F:$T, the conditional formatting results are all over the place, highlighting random cells, more wrong results than right ones.

 

 

 

Can anyone help with this issue? 

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    ebella212 

    All formatting is shifted by one row. The format of row 1 is determined by Yes or No in row 2, and row 2's format depends on row 3 etc. 

    This is because you apply a rule looking at $U2 applied to entire columns F:T that start in row 1.

     

    So, change $U2 into $U1 or set the applied range to $F$2:$T$5000, for example. Any range big enough to capture all rows with data. Avoid using references to whole columns as the may slow down your file if you have many of them.

  • ebella212 

    Your conditionally formatted region is $F:$T, that is a range that starts at cell F1.  That means your conditional formatting formulas should be specified relative to F1, not F2. 

    = $U1 = "yes"
    
    or, equvalently
    
    = $J1=$M1