Excel Forum

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRow As Long
    Dim iFormat As String
    iRow = Target.Row
    If Target.Column = 3 And iRow >= 6 Then 'Column=3 (column C) iRow>=6 (start row)
        Select Case Range("C" & iRow)
            Case "J": iFormat = "\#,##0;\-#,##0"
            Case "T": iFormat = ChrW(3647) & "#,##0.00;-" & ChrW(3647) & "#,##0.00"
        End Select
        'Range("L" & iRow & ",M" & iRow & ",N" & iRow & ",O" & iRow & ",P" & iRow & ",Q" & iRow).NumberFormatLocal = iFormat
        Range("L" & iRow & ":Q" & iRow).NumberFormatLocal = iFormat
    End If
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iRange As Range
    Dim iFormat As String
    If Not Application.Intersect(Target, Range("C6:C10000")) Is Nothing Then
        For Each iRange In Target.Rows
                Select Case Range("C" & iRange.Row)
                    Case "J": iFormat = "\#,##0;\-#,##0"
                    Case "T": iFormat = ChrW(3647) & "#,##0.00;-" & ChrW(3647) & "#,##0.00"
                End Select
                Range("L" & iRange.Row & ":Q" & iRange.Row).NumberFormatLocal = iFormat
        Next
    End If
End Sub