Forum Discussion

Space250's avatar
Space250
Copper Contributor
Feb 22, 2023
Solved

Conditional formatting of a single cell by row

Hi all,    After doing some research, I am a little stuck on how to make this happen. The closest I found was this topic, but it didn't work out.   I am trying to change the color of a cell based...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 22, 2023

    Space250 

    =COUNTIF($C2:$AG2,"JT")+COUNTIF($C2:$AG2,"JK")

    This is the rule for conditional formatting. There's only a slight change to your formula. With this rule you don't need a value in AI2, AI3 and so on.

    =$B$2:$B$27

    This is the range the format applies to in the example.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 22, 2023

Space250 

My formula for the yellow cells A12:A15 differs from yours simply because I use Excel 365 and there are differences of style

= BYROW(data,
    LAMBDA(aRow, 
      SUM(
        COUNTIFS(aRow, {"JK","JT"})
      )
    )
  )

The conditional formats, though, will be the same since Conditional Formatting relies upon antiquated code.

The CF is based upon a formula which is no more than a relative reference to cells within the range A12:A15.  The numbers 1 or 2 are treated as TRUE whilst the 0s are FALSE.

 

Resources