I'm experiencing an issue where the manual check of formatting for a particular cell does not match what the Range.DisplayFormat.NumberFormat returns (I've tried .NumberFormatLocal also). It appears it is replacing parenthesis characters () with a hyphen -.
What I'm trying to do:
A client has provided a Template workbook in which we are to automate to open and compare other workboooks they want to standardize to the template workbook. They want a Cell-For-Cell comparision in things such as formatting, borders, values, etc. At this stage, what I'm trying to do is compare cell formatting between the same cell position (e.g. N11) between the workbooks for standardization.
My Problem:
In this case as an example, using cell N11, when I check the custom formatting manually by right clicking the cells I see that they match:
Workbook A cell N11: _($* #,##0_ );_($* (#,##0);_($* "-"??_);_(@_)
Workbook B cell N11: _($* #,##0_ );_($* (#,##0);_($* "-"??_);_(@_)
When I run the code in VBA for comparison in the automation, using the Locals window, it produces the following with the formatting for Workbook A (template) drastically different. It is replacing parenthesis with hyphens and adding ".00":
Workbook A cell N11: "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_-" <--- this is wrong and not what is in formatting window
Workbook B cell N11: "_($* #,##0_ );_($* (#,##0);_($* "-"??_);_(@_)"
(note: I don't know why above the color formatting here is orange or blue. I only used red to highlight the differences)
This is the function code I'm using to capture the formatting of a cell for comparison and the call to the function into a string variable
What I'm trying to do:
A client has provided a Template workbook in which we are to automate to open and compare other workboooks they want to standardize to the template workbook. They want a Cell-For-Cell comparision in things such as formatting, borders, values, etc. At this stage, what I'm trying to do is compare cell formatting between the same cell position (e.g. N11) between the workbooks for standardization.
My Problem:
In this case as an example, using cell N11, when I check the custom formatting manually by right clicking the cells I see that they match:
Workbook A cell N11: _($* #,##0_ );_($* (#,##0);_($* "-"??_);_(@_)
Workbook B cell N11: _($* #,##0_ );_($* (#,##0);_($* "-"??_);_(@_)
When I run the code in VBA for comparison in the automation, using the Locals window, it produces the following with the formatting for Workbook A (template) drastically different. It is replacing parenthesis with hyphens and adding ".00":
Workbook A cell N11: "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_-" <--- this is wrong and not what is in formatting window
Workbook B cell N11: "_($* #,##0_ );_($* (#,##0);_($* "-"??_);_(@_)"
(note: I don't know why above the color formatting here is orange or blue. I only used red to highlight the differences)
This is the function code I'm using to capture the formatting of a cell for comparison and the call to the function into a string variable
Code:
Public Function CheckFormatting(fmt As Range)
CheckFormatting = CStr(fmt.[B]DisplayFormat.NumberFormat[/B])
End Function
' the call.....
Dim formatT As String: formatT = CheckFormatting(fmtT)
Dim formatC As String: formatC = CheckFormatting(fmtC)
Last edited:
