When two cells report being different from each other until you activate them (F2) and press Enter, and then they report being the same as each other, what was the difference between them really, and is it possible to detect that difference without F2+Enter?

I'm comparing many extremely large sheets -- several million cells in the used range (sheet.UsedRange), and many of them over 100MB -- and reporting any differences found. Often only a few differences are found among the several millions cells.

To make the comparison faster, for each sheet being compared I'm setting the used range to a variant, turning the variant into a variant array:
Dim myVariantArray as Variant 'do NOT declare as array.
myVariantArray = mysheet.UsedRange

But sometimes it reports two cells -- two Variants in the Variant array -- as being different (VariantA = VariantB returns False) when I don't see any difference:
-- They are the same string (same characters).
-- They have the same number of characters (len).
-- Each character has the same Asc values and the same AscW values.
-- They have the same Typename and the same VarType.

When I go back to the source cells on the sheets from which the Variants in the array were made, the two cells also report as being different from each other (cellA = cellB returns False) and I find all of the above plus:
-- They have the same number formatting.

But if I just press F2+Enter, they cells report as being the same as each other (cellA = cellB returns True).

Using SendKeys to send F2 + Enter isn't a practical solution because the user will be running a report in the background that does comparisons like that on many sets of extremely large sheets, while the user does other work in the foreground.


So is there any other way to detect the fact that they are the same without doing the F2+Enter?

I'm especially interested in what is different in the Variants. In one example I'm looking at, they report a VarType of 5, indicating Double, and their Typename is also Double. So I don't think there is an object hiding in the Variant with some mysterious complexity. What is the difference, really, between those two Variants?


Thanks,

Greg