This is more a curiosity question than a fix to a problem, because I found a few ways to fix it already.
From what I could gather, if I want to apply a conditional formatting based on the value of another cell, I have to use a custom formula. However, in the list, there are a couple options that would seem to be much more relevant: "Greater than (or equal to)", "Lesser than (or equal to)", "Is (not) equal to", "Is (not) between". But I'm starting to think that they are not so useful.
Here I have a column of numbers ranging from 10 to 30; I want to highlight them in green if they're greater than or equal to a number (20) set in another cell. From a few other questions here and on SO, it would seem that I should use a custom formula as something like
=K7>=K4, but instead I still wanted to try the options in the list that seemed more intuitive.
As you can see though, it does some weird things, where it consider 11 to be under 20 (which is true AFAIK), but everything else above 11 is either equal or greater than 20.
My guess is that putting
=K4 in the "Value or formula" field didn't quite link to the actual K4 cell, but I can't figure out why it does what it does here.
If I put "20" in the field, it highlights the right cells (but it's not dynamic anymore).
=K$4 works like a charm. Quite literally, in this case.