Rick Rothstein
04-14-2012, 02:27 AM
In my previous article on this subject (see "Excel Number Format: Indian Style Comma Separation (http://www.excelfox.com/forum/f22/excel-number-format-indian-style-comma-separation-375/)"), I provided event code that would automatically format cells containing constant values (that is, numbers that were not the result of a formula). Of course, there are occasions when you might want to use a formula to reference other cells (perhaps as part of a calculation) to produce your number which you would then want to format using the Indian Stype Comma Separation. Okay, with a somewhat large "BUT", here is code to do that (note... this code automatically handles both constant numbers and numbers that are the result of a formula)...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, Intersection As Range, Cell As Range, Pattern As String
' You set the range of cells that will be formatted with the Indian Comma Style here
' For this example, any cell in Column A or in the cells in E3:H10 will be formatted
Set R = Range("A:A,E3:H10")
On Error GoTo NoDependents
If Not Intersect(Target, R) Is Nothing Then
Set Intersection = Intersect(Target, R)
ElseIf Not Intersect(Target.Dependents, R) Is Nothing Then
Set Intersection = Intersect(Target.Dependents, R)
End If
For Each Cell In Intersection
If WorksheetFunction.IsNumber(Cell.Value) Then
Pattern = Trim(Replace(Format(String(Len(Int(Cell.Value)) - 1, "#"), _
" @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", ""))
If Len(Pattern) = 0 Then Pattern = "0"
Cell.NumberFormat = Pattern & ".00"
Else
Cell.NumberFormat = "General"
End If
Next
NoDependents:
End Sub
Now, about that somewhat large "BUT"... the formula part of this code works only if the cell(s) being referenced by the formula is on the same worksheet as the formula itself; that is, if a formula references a cell on another worksheet, this code will not react to changes to that cell. The problem has to do with the way Excel implements its Precedents/Dependents object model across worksheets (which, in my view, it does quite poorly). I do have an idea on how to code around the limitations for the Precedents/Dependents object model, but it would (to my mind's eye) be very, very cumbersome. If I can figure out how to make it more efficient, I will post a "Part 3" article to this particular subject, but in the meantime, you have a method to use for the case when your formulas are referencing local cells. Just to point out, though, if you have formulas referencing cells on other worksheets, you could place a formula pointing to that "foreign" cell on the worksheet with the formula and then change the formula's cell reference from that foreign cell to the local cell contain the formula which is pointing to that foreign cell... if you do that, the code I posted will work fine.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, Intersection As Range, Cell As Range, Pattern As String
' You set the range of cells that will be formatted with the Indian Comma Style here
' For this example, any cell in Column A or in the cells in E3:H10 will be formatted
Set R = Range("A:A,E3:H10")
On Error GoTo NoDependents
If Not Intersect(Target, R) Is Nothing Then
Set Intersection = Intersect(Target, R)
ElseIf Not Intersect(Target.Dependents, R) Is Nothing Then
Set Intersection = Intersect(Target.Dependents, R)
End If
For Each Cell In Intersection
If WorksheetFunction.IsNumber(Cell.Value) Then
Pattern = Trim(Replace(Format(String(Len(Int(Cell.Value)) - 1, "#"), _
" @@\\,@@\\,@@\\,@@\\,@@\\,@@\\,@@0"), " \,", ""))
If Len(Pattern) = 0 Then Pattern = "0"
Cell.NumberFormat = Pattern & ".00"
Else
Cell.NumberFormat = "General"
End If
Next
NoDependents:
End Sub
Now, about that somewhat large "BUT"... the formula part of this code works only if the cell(s) being referenced by the formula is on the same worksheet as the formula itself; that is, if a formula references a cell on another worksheet, this code will not react to changes to that cell. The problem has to do with the way Excel implements its Precedents/Dependents object model across worksheets (which, in my view, it does quite poorly). I do have an idea on how to code around the limitations for the Precedents/Dependents object model, but it would (to my mind's eye) be very, very cumbersome. If I can figure out how to make it more efficient, I will post a "Part 3" article to this particular subject, but in the meantime, you have a method to use for the case when your formulas are referencing local cells. Just to point out, though, if you have formulas referencing cells on other worksheets, you could place a formula pointing to that "foreign" cell on the worksheet with the formula and then change the formula's cell reference from that foreign cell to the local cell contain the formula which is pointing to that foreign cell... if you do that, the code I posted will work fine.