compare cell formats

[Guest]

I need a simple boolean function that will return TRUE if the formats of two
cells are identical, otherwise FALSE. (the contents of the cells are always
empty)


For example =formatest(A1,B2)

 

[Guest]

Public Function FormatTest(rng1 as Range, rng2 as Range) as Boolean
FormatTest = False
if rng2.count > 1 or rng1.count > 1 then exit sub
If rng2.numberformat = rng1.numberformat then
FormatTest = True
end if
End Function

This is pretty literal. It will show false for

"#,##0" = "#,##0;-#,##0"

another approach might be

Public Function FormatTest(rng1 as Range, rng2 as Range) as Boolean
FormatTest = False
if rng2.count > 1 or rng1.count > 1 then exit sub
If rng2.text = Format(rng2.value2,rng1.numberformat) and _
rng1.Text = Format(rng1.value2,rng2.numbeformat) then
FormatTest = True
end if
End Function

 

[Guest]

Hi Tom:

Thanks for the fast response.

However your function returns TRUE for two cells, one of which is
left-justified and the other right-justified.

I am working with a worksheet that appeared to be completely empty. It was
over 800K in size! Although all the cells are empty, ActiveSheet.UsedRange
covered over 2000 cells.

I discovered that the information has been encoded in the cell's formats
rather than the cell's contents. The cells have different fonts, font sizes.
Some are bold, some are underlined. Some are right-justified, some
left-justified.

None of the cells have contents, background colors or borders; so none of
the formatting differences are visible to the naked eye.

I need the formula to, at least, identifiy which cells have information
associated with them.

Thanks again for your help.

 

[Peter T]

I need the formula to, at least, identifiy which cells have information

associated with them.

There is no simple way to determine if a cell has been applied with one or
more non default format properties, whether inside or outside the UsedRange.

ALL cells have well over 30 individual format properties. You would need to
look each of these properties and compare with those of given cell or, to
check if not same as default, compare with the Normal style properties. Lot
of work and slow.

What's the overall objective, probably a simpler solution.

Regards,
Peter T

What is the purpose

 

[Guest]

Hi: Peter:

First, thank you very much for responding. I am proceeding with something
similar to your comments. I now have a set of over 40 very small (usually
1-3 lines) macros that capture the formatting information for a cell and
deposit into an array.

I can then compare the cell's array to an array for a "default" cell.
Fortunately ActiveSheet.UsedRange covers all formatted cells.

Thanks again.

 

[Peter T]

40 macros becuase you need to return all formats at the same time - forgive
me but that sounds dreadful!

Why not just the one function

with cel
' lots of formats attach direct to .cel, then
with .interior .. .colorindex, .patteren, patterncolorindex, end with
with .font - .name, .size, .bold about 12 things ' return to a variant
in case mixed
for each bdr in .borders ' 18 potential formats
for each fc in .formatconditions

Fortunately ActiveSheet.UsedRange covers all formatted cells.

Not necessarily, formats in entire rows or columns are typically outside the
UR

Regards,
Peter T

 

[Guest]

Thanks again Peter:

1. I'll take your advise on combining the tiny macros.
2. I'll also carefully examine cells outside the UR to check for formats

This is the kind of assignment I really dis-like. I'll probably never use
this stuff again in the future.