Forum Discussion
Conditional formatting for a whole column, based on same-row-cells, but in one single rule
I need conditional formatting, where the color of the cell in column F is changed based on the value in the cell in column W in the same row:
I have done it like this:
The cell F119 changes colour to orange if this is true:
=OG($W$119<>"x";$W$119<>"CNG")
This works fine. But I have not been able to find a way to make one rule do this for the entire column.
So I have set an individual rule for each cell in column F and I have 376 rows so far.
When I need to change the rule, or troubleshoot the rule, this is not operable.
Also a lot of the cells have been edited with copy-paste, and now the conditional formatting is all over the place 😒
I would prefer one single rule, that checks the cell in column W in each row,
and changes the colour in cell F in the same row, if the conditions are true.
Hi you are so close. So
a) set the "applied to" range to be all of the cells you need it to apply to (e.g. F119:F229)
b) change the formula to NOT have the $ in front of the row numbers (and make sure the row number corresponds to the upper left of the "applied to" range)
so in your example if the applied to range is F119:G229 (i.e. row 119 is the FIRST / upper left corner of the "applied to range")
Then the formula would be:
=OG($W119<>"x";$W119<>"CNG")The $ in front of the W can stay or go since you are only applying it to a single column. Basically that $ mean do NOT change it relative to the applied to cell but withOUT the $ it WILL change relative to the applied to cell.
2 Replies
Thank you so much, m_tarler​ - that was just what I needed!
Hi you are so close. So
a) set the "applied to" range to be all of the cells you need it to apply to (e.g. F119:F229)
b) change the formula to NOT have the $ in front of the row numbers (and make sure the row number corresponds to the upper left of the "applied to" range)
so in your example if the applied to range is F119:G229 (i.e. row 119 is the FIRST / upper left corner of the "applied to range")
Then the formula would be:
=OG($W119<>"x";$W119<>"CNG")The $ in front of the W can stay or go since you are only applying it to a single column. Basically that $ mean do NOT change it relative to the applied to cell but withOUT the $ it WILL change relative to the applied to cell.
