conditional format: affected by cell size

H

Hugh Murfitt

I have used conditional formatting to highlight when text in two adjacent
cells differ. Simple. Therefore, when text in cell B1 differs from that in
A1, B1 goes green. UNLESS the text in A1 is longer than 255 characters. How
can I get round this?
 
D

Dave Peterson

How about a couple of ways to cheat?

If you have up to 511 characters per cell:
=OR(MID(A1,1,255)<>MID(B1,1,255),MID(A1,256,255)<>MID(B1,256,255))
(just breaking it down into groups of 255--add more if you need them)

Or use another cell that looks like:
=a1<>b1
and use that cell as the conditional formatting rule.
 
R

RagDyeR

I *cannot* duplicate your problem using this formula in Column B:

=AND(A1<>"",B1<>"",A1<>B1)

Exactly what formula are you using?
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

I have used conditional formatting to highlight when text in two adjacent
cells differ. Simple. Therefore, when text in cell B1 differs from that in
A1, B1 goes green. UNLESS the text in A1 is longer than 255 characters.
How
can I get round this?
 
P

Peter Ellis

Change the value in the condition1 is to "formula is" from "cell value is"

Then try the following formula in the conditional format
=AND(b1<>a1,LEN(a1)>255)
 
H

Hugh Murfitt

Thanks a million for your help.

I used your suggestion of =a1<>b1, but as the Conditional Formatting
"Formula is" in cell b1. The Conditional Formatting Formula doesn't seem to
affected by cell size.

(My largest cell was 866 characters)

Best regards

Hugh
 
H

Hugh Murfitt

Thank you Peter, and RagDyeR.

Got a result!

Peter Ellis said:
Change the value in the condition1 is to "formula is" from "cell value is"

Then try the following formula in the conditional format
=AND(b1<>a1,LEN(a1)>255)
Click to expand...
 
Top