Your formulas are all array formulas.

The first will return an array of over a million TRUE/FALSE values. Unfortunately conditional formatting is antiquated functionality and does not work properly with Boolean arrays; it will only act on the first value and so your entire conditional format range turns red if** $G$2>1000**.

The other conditions produce two such Boolean arrays but then the AND requires every one of the two million conditions to be TRUE in order to return the single result as TRUE [**AND **is a bit like **SUM **in that it takes many input values and produces only a single result]. When using array conditions one tends to resort to multiplying the conditions to get them to calculate pairwise.

**= ($G$2:$G$1048576>=500) * ($G$2:$G$1048576<=1000) **

It doesn't help in this case, though. To work in a conditional format, you need to test each cell individually, using a relative reference, starting with the top-left cell of the range to which you apply the format.

A side product of the fact that CF does not work properly with arrays is that dragging and dropping values form one location to another wrecks the definition of the range to which the CF is applied. Many workbooks have unintended multi-area ranges for CF.