Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell formats
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) -- Gary's Student |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell formats
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 -- Regards, Tom Ogilvy "Gary''s Student" wrote: 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) -- Gary's Student |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell formats
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. -- Gary''s Student "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Gary''s Student" wrote: 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) -- Gary's Student |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell formats
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 "Gary''s Student" wrote in message ... 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. -- Gary''s Student "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Gary''s Student" wrote: 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) -- Gary's Student |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell formats
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. -- Gary's Student "Peter T" wrote: 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 "Gary''s Student" wrote in message ... 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. -- Gary''s Student "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Gary''s Student" wrote: 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) -- Gary's Student |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell formats
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 "Gary''s Student" wrote in message ... 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. -- Gary's Student "Peter T" wrote: 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 "Gary''s Student" wrote in message ... 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. -- Gary''s Student "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Gary''s Student" wrote: 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) -- Gary's Student |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell formats
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. -- Gary's Student "Peter T" wrote: 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 "Gary''s Student" wrote in message ... 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. -- Gary's Student "Peter T" wrote: 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 "Gary''s Student" wrote in message ... 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. -- Gary''s Student "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Gary''s Student" wrote: 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) -- Gary's Student |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formats - column widths, formats, outlining to worksheets | Excel Worksheet Functions | |||
Inheriting cell formats when using absolute cell references | Excel Discussion (Misc queries) | |||
Copy and link formats from cell to cell | Excel Discussion (Misc queries) | |||
How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
Formats: Too many different cell formats error message | Excel Programming |