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.
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).
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.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
Any help on this is much appreciated!!