Cells aren't color coding?

[Grammarjunkie]

=IF(AND(D12="Fixed",D26<>J75,G50=J75,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),IF(AND(D12="Fixed",D26<>J75,G50=J75,OR(D26-J75<0.125%,D26-J75>0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50>0.125%,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and  Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50>0.125%,OR(D26-J75<0.125%,D26-J75>-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50<0.125%,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50<0.125%,OR(D26-J75<0.125%,D26-J75>-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D12="Fixed",D26=J75,J75>G50,J75-G50>0.125%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="W5libio225",D26<>J75,G50=J75,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),IF(AND(D19="W5libio225",D26<>J75,G50=J75,OR(D26-J75<0.25%,D26-J75>0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and  Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D12="W5libio225",D26=J75,J75>G50,J75-G50>0.25%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,G50=J75,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,G50=J75,OR(D26-J75<0.25%,D26-J75>0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and  Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),IF(AND(D12="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26=J75,J75>G50,J75-G50>0.25%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),"")))))))))))))))))))))

I have this formula in cell L60, and it seems to be working how I want it. At least by all the testing I've done so far. But I just noticed the individual cells (when I'm in the formula box) aren't color coding. Like how it normally does one green, blue, etc. Which I find convenient!
Normally when the cell is just black with no color, that means I've forgotten a comma or a parenthesis; it's a good indication something is going to fail. But as I said, I'm not getting an error or anything.

And it's not even like halfway through they stop color coding. It's from the very first cell.

Any idea why they're not color coding?

 

[mole999]

colour coding is normally via conditional format based on what your cell returns, wondering if I have missed something (did the work tested as individuals)

=
IF(AND(D12="Fixed",D26<>J75,G50=J75,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),

IF(AND(D12="Fixed",D26<>J75,G50=J75,OR(D26-J75<0.125%,D26-J75>0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),

IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50>0.125%,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),

IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50>0.125%,OR(D26-J75<0.125%,D26-J75>-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),

IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50<0.125%,OR(D26-J75>0.125%,D26-J75<-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),

IF(AND(D12="Fixed",D26<>J75,J75>G50,J75-G50<0.125%,OR(D26-J75<0.125%,D26-J75>-0.125%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),

IF(AND(D12="Fixed",D26=J75,J75>G50,J75-G50>0.125%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),

IF(AND(D19="W5libio225",D26<>J75,G50=J75,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),

IF(AND(D19="W5libio225",D26<>J75,G50=J75,OR(D26-J75<0.25%,D26-J75>0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),

IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),

IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),

IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),

IF(AND(D19="W5libio225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),

IF(AND(D12="W5libio225",D26=J75,J75>G50,J75-G50>0.25%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),

IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,G50=J75,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance"),

IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,G50=J75,OR(D26-J75<0.25%,D26-J75>0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not equal but within tolerance"),

IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),

IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50>0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),

IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75>0.25%,D26-J75<-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") not within tolerance, and difference between Final CD (",TEXT(J75,"0.000%"),") and Preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance"),

IF(AND(D19="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26<>J75,J75>G50,J75-G50<0.25%,OR(D26-J75<0.25%,D26-J75>-0.25%)),CONCATENATE("APR difference between ELC (",TEXT(D26,"0.0000%"),") and Final CD (",TEXT(J75,"0.000%"),") within tolerance, and APR difference between Final CD (",TEXT(J75,"0.000%"),") and preliminary CD (",TEXT(G50,"0.000%"),") not within tolerance."),

IF(AND(D12="c_p10lib225",D9="c_p1lib225",D9="c_p2lib225",D9="c_p3lib225",D9="c_p5lib225",D9="c_p7lib225",D26=J75,J75>G50,J75-G50>0.25%),CONCATENATE("APR difference between Final CD (",TEXT(J75,"0.000%"),") and Prelim CD (",TEXT(G50,"0.000%"),") not within tolerance"),"")))))))))))))))))))))

 

[mole999]

worried by the red area (and there are more) D9 cannot equal all of those at the same time, you need to work in an OR

 

[Grammarjunkie]

worried by the red area (and there are more) D9 cannot equal all of those at the same time, you need to work in an OR

You're right. I meant to use OR(). Just got ahead of myself.

But as for the "color coding," I'm not talking about conditional formatting. I'm talking about how when you click in the formula box, the cells are colored for ease of reading. And then the cell in the spreadsheet is outlined in the corresponding color.

 

[mole999]

AH, not sure about that, but I know what you mean now

 

[mole999]

Is it just that formula, or any workbook ?

 

[Asala42]

You're hitting some kind of limitation, probably the number of range references.

When I plug in your formula I see the same thing you do. The formula bar does not color code the cell references, though it does color code the parentheses. If I shrink the formula artificially, somewhere in the 5300 formula length range (roughly), the color coding returns.