Forum Discussion
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 on the presence or absence of two text triggers in a given row. I calculate this at first with:
=COUNTIF(C2:AG2,"JT")+COUNTIF(C2:AG2,"JK")
... this then results with usually a 0 or a 1 (occasionally a 2).
What I'd like is the result of that to then trigger another cell elsewhere in the sheet to be colored or not. In English (as I don't know how to code it)
If AI2 is >= 1 then format cell B2 with a color
If it was only B2 that needed to be colored, this would be easy with conditional formatting. However, for my case I need this to be calculated for each individual row. In other words, B3 should change color when AI3 >=1, B4 should change color with AI4 >=1 (etc.)
Thanks so much for any help!
=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$27This is the range the format applies to in the example.
=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$27This is the range the format applies to in the example.
I must be missing something simple, but I am not sure how to get the Conditional Formatting Pane to allow me to change the "Applies To" section? When I double click it, it edits the original formula instead?
(I am doing this on macOS so maybe that's why?) OliverScheurichI don't work with macOS and therefore can't tell if that could cause a problem.
I assume it should work if you firstly delete all conditional formats of this sheet / range. Then you can select the applies to range (B2:B27 in the example) with the mouse as shown in the screenshot. Then you can start conditional formatting -> new rule and so on. That's the way i work with conditional formatting.
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.
