Conditional Formats Not Working Properly When Copied

**Oscar Martin** · 01-28-2014, 01:22 PM

Hi there,

I have created very specific conditional formats to switch on when certain logistical features are present in a booking. You will see that these work fine in the 'Conditional Formats' page of the workbook I've uploaded. However when copied to the 'New Data' page and applied to new data some of them become dysfunctional.

For example the 'red' format is only meant to switch on when the 'Mode' column is 'Road', the 'Booker' column is greater than 0, the 'Origin Agent' is greater than 0, the Destination Agent is greater than 0, the 'Weight' column is greater than 0, the 'Estimated Departure Date' is greater than 0 and the 'Estimated Arrival Date' is greater than 0.

As you will see most of the bookings in the 'New Page' highlighted red do not meet the conditions of this format but are still red. The bookings highlighted red in the 'Conditional Formats' page are correctly meeting these conditions.

This happens throughout the 'New Page' with a number of the conditional formats I have copied over. I cannot see the logic at play here.

Any help will be much appreciated.

Rgds,

Oscar.

**6StringJazzer** · 01-28-2014, 01:43 PM

In columns Y and AA of New Data, you have some sort of content that evaluates as >0 even though it looks blank. If you hit DELETE on those empty cells, the formatting returns to how you want it. How did you populate the data in that sheet?

Using a comparison of >0 is good for numeric data, but not such a great way to check for blanks. I would use this formula, which is immune to the problem above:

Formula:

Please Login or Register  to view this content.

**Pepe Le Mokko** · 01-28-2014, 01:48 PM

This happens because you are using a condition that triggers something when a cell containing text is larger than 0.

For instance M7 looks empty but is in fact a null text string.

When excel compares the content of two cells it first compares the value of the TYPE information the cell contains (without you knowing)

If you check the syntax of the TYPE() function you will see that TYPE(text) =2 and TYPE(number) =1. (and other values for other cell contents)

The TYPE(M7) is thus=2 which is larger than TYPE(0) and thus returns TRUE - Your other conditions being satisfied you get a red formatting which is not what you want.

If you change =AND(O6="Road",D6>0,K6>0,M6>0,U6>0,Y6>0,AA6>0) to =AND(O6="Road",D6>0,K6>0,M6<>"",U6>0,Y6>0,AA6>0) the formatting will change.

The best thing to do would be to change all your conditions involving text to <>"" instead of >0