• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting does not work

I have created 3 conditional formatting rule for a column

=$G$2:$G$1048576 >1000 (Fill: Red)
=AND($G$2:$G$1048576>=500, $G$2:$G$1048576<=1000) (Fill: Amber)
=AND($G$2:$G$1048576>=1, $G$2:$G$1048576<=499) (Fill: Green)

The cell was highlighted whenever i enter the data. Please advise what would be the issue here.

Thanks in advance
 

pecoflyer

Active Member
We need to see your sheet but there is a chance your values are text looking like numbers. ( to check remove any manual alignment, if left-aligned the contents are text)
To correct, select the Range - Data -Text to columns - Finish ( contents should now be right-aligned)

Also your CF does not need to address a range
Just select the range and enter =$G2>1000
Correct the other CF accordingly. The CF will adapt to the selected range
Selecting entire columns for CF is a bad idea, it will make your sheet large and slow it down.
Eventually use a smaller range to select ( say G2:G100) or use dynamic ranges
 
We need to see your sheet but there is a chance your values are text looking like numbers. ( to check remove any manual alignment, if left-aligned the contents are text)
To correct, select the Range - Data -Text to columns - Finish ( contents should now be right-aligned)

Also your CF does not need to address a range
Just select the range and enter =$G2>1000
Correct the other CF accordingly. The CF will adapt to the selected range
Selecting entire columns for CF is a bad idea, it will make your sheet large and slow it down.
Eventually use a smaller range to select ( say G2:G100) or use dynamic ranges
I have tried using your method but the cells were not highlighted.
 

Attachments

vletm

Excel Ninja
liabilityquek
Your However, i would like to also know why doesn't my formula work, unless my formula is off..
eg RED : Your given the whole range is not over 1000 ... only few are =B2>1000 ... if any selected range (applies to) is over 1000 then RED
with others ... You have same unwanted features.
 

Peter Bartholomew

Well-Known Member
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.
 
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.
Appreciate the advice..
 
Top