deadstick
04-10-2019, 06:44 AM
Hello all!
I have been searching everywhere but cannot find a solution to my problem. It should be simple, but maybe I am just missing it.:banghead:
I have three columns that contain the Dimension (D), Max tolerance (E), and Min tolerance (F) from a technical drawing. In the same row, there are five more columns for measured data. All fields are user entry. The user will format the number of decimal places in Columns D - F based on what is called out on the print. I want a way to automatically format the data fields (Columns G - K) to one more decimal place than the highest in Columns D - F.
I have the following code that works row by row, but can't figure out a way to make it work for all rows (rows can be added and deleted).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
For Each cell In Range("G2:K2")
If IsNumeric(cell.Value) And cell.Value <> "" Then
If Range("D2").NumberFormat = "0.0000" Or _
Range("E2").NumberFormat = "0.0000" Or _
Range("F2").NumberFormat = "0.0000" Then
cell.NumberFormat = "0.00000"
ElseIf Range("D2").NumberFormat = "0.000" Or _
Range("E2").NumberFormat = "0.000" Or _
Range("F2").NumberFormat = "0.000" Then
cell.NumberFormat = "0.0000"
ElseIf Range("D2").NumberFormat = "0.00" Or _
Range("E2").NumberFormat = "0.00" Or _
Range("F2").NumberFormat = "0.00" Then
cell.NumberFormat = "0.000"
ElseIf Range("D2").NumberFormat = "0.0" Or _
Range("E2").NumberFormat = "0.0" Or _
Range("F2").NumberFormat = "0.0" Then
cell.NumberFormat = "0.00"
ElseIf Range("D2").NumberFormat = "0" Or _
Range("E2").NumberFormat = "0" Or _
Range("F2").NumberFormat = "0" Then
cell.NumberFormat = "0.0"
End If
End If
Next cell
For Each cell In Range("G3:K3")
If IsNumeric(cell.Value) And cell.Value <> "" Then
If Range("D3").NumberFormat = "0.0000" Or _
Range("E3").NumberFormat = "0.0000" Or _
Range("F3").NumberFormat = "0.0000" Then
cell.NumberFormat = "0.00000"
ElseIf Range("D3").NumberFormat = "0.000" Or _
Range("E3").NumberFormat = "0.000" Or _
Range("F3").NumberFormat = "0.000" Then
cell.NumberFormat = "0.0000"
ElseIf Range("D3").NumberFormat = "0.00" Or _
Range("E3").NumberFormat = "0.00" Or _
Range("F3").NumberFormat = "0.00" Then
cell.NumberFormat = "0.000"
ElseIf Range("D3").NumberFormat = "0.0" Or _
Range("E3").NumberFormat = "0.0" Or _
Range("F3").NumberFormat = "0.0" Then
cell.NumberFormat = "0.00"
ElseIf Range("D3").NumberFormat = "0" Or _
Range("E3").NumberFormat = "0" Or _
Range("F3").NumberFormat = "0" Then
cell.NumberFormat = "0.0"
End If
End If
Next cell
End Sub
I tried creating dynamic named ranges and placing that in my code, but that did not seem to work. I have also attached a sample file.
Any help on this is much appreciated!!
I have been searching everywhere but cannot find a solution to my problem. It should be simple, but maybe I am just missing it.:banghead:
I have three columns that contain the Dimension (D), Max tolerance (E), and Min tolerance (F) from a technical drawing. In the same row, there are five more columns for measured data. All fields are user entry. The user will format the number of decimal places in Columns D - F based on what is called out on the print. I want a way to automatically format the data fields (Columns G - K) to one more decimal place than the highest in Columns D - F.
I have the following code that works row by row, but can't figure out a way to make it work for all rows (rows can be added and deleted).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
For Each cell In Range("G2:K2")
If IsNumeric(cell.Value) And cell.Value <> "" Then
If Range("D2").NumberFormat = "0.0000" Or _
Range("E2").NumberFormat = "0.0000" Or _
Range("F2").NumberFormat = "0.0000" Then
cell.NumberFormat = "0.00000"
ElseIf Range("D2").NumberFormat = "0.000" Or _
Range("E2").NumberFormat = "0.000" Or _
Range("F2").NumberFormat = "0.000" Then
cell.NumberFormat = "0.0000"
ElseIf Range("D2").NumberFormat = "0.00" Or _
Range("E2").NumberFormat = "0.00" Or _
Range("F2").NumberFormat = "0.00" Then
cell.NumberFormat = "0.000"
ElseIf Range("D2").NumberFormat = "0.0" Or _
Range("E2").NumberFormat = "0.0" Or _
Range("F2").NumberFormat = "0.0" Then
cell.NumberFormat = "0.00"
ElseIf Range("D2").NumberFormat = "0" Or _
Range("E2").NumberFormat = "0" Or _
Range("F2").NumberFormat = "0" Then
cell.NumberFormat = "0.0"
End If
End If
Next cell
For Each cell In Range("G3:K3")
If IsNumeric(cell.Value) And cell.Value <> "" Then
If Range("D3").NumberFormat = "0.0000" Or _
Range("E3").NumberFormat = "0.0000" Or _
Range("F3").NumberFormat = "0.0000" Then
cell.NumberFormat = "0.00000"
ElseIf Range("D3").NumberFormat = "0.000" Or _
Range("E3").NumberFormat = "0.000" Or _
Range("F3").NumberFormat = "0.000" Then
cell.NumberFormat = "0.0000"
ElseIf Range("D3").NumberFormat = "0.00" Or _
Range("E3").NumberFormat = "0.00" Or _
Range("F3").NumberFormat = "0.00" Then
cell.NumberFormat = "0.000"
ElseIf Range("D3").NumberFormat = "0.0" Or _
Range("E3").NumberFormat = "0.0" Or _
Range("F3").NumberFormat = "0.0" Then
cell.NumberFormat = "0.00"
ElseIf Range("D3").NumberFormat = "0" Or _
Range("E3").NumberFormat = "0" Or _
Range("F3").NumberFormat = "0" Then
cell.NumberFormat = "0.0"
End If
End If
Next cell
End Sub
I tried creating dynamic named ranges and placing that in my code, but that did not seem to work. I have also attached a sample file.
Any help on this is much appreciated!!