Forum Discussion
- Riny_van_EekelenPlatinum Contributor
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.
- PeterBartholomew1Silver Contributor
Hi Riny
You beat me to it! I do so hate the concept of relative referencing; it is an unnecessary mess.
Maybe it is an act of heresy to say so on a spreadsheet forum though 😮!
Peter
- Riny_van_EekelenPlatinum Contributor
- ebella212Copper Contributor
Riny_van_Eekelen Oh my goodness such a simple mistake! Thank you so much!!
- PeterBartholomew1Silver Contributor
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