Adding line to macro for number formatting

[bluepenink]

Hi all

I have the below noted macro, unfortunately, i am not well versed with macros.

i am basically looking to add a line in the macro below so that it will fix the number format when it "paste" the data. Currently, it shows ie. 5000.87 -> i would like for it to show the amount as 5,000.

can someone help?

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet
Dim lr1 As Long, lr2 As Long, lr3 As Long, lr4 As Long, sn As Variant, arr As Variant, rng As Range
Set ws1 = Sheets("Summary")
Set ws2 = Sheets("WIP Table")
Set ws3 = Sheets("AR Table")
Set ws4 = Sheets("WIP Data")
Set ws5 = Sheets("AR Data")

If Target.Address = "$E$7" Then
    lr1 = ws2.Range("C" & Rows.Count).End(xlUp).Row
    lr2 = ws3.Range("C" & Rows.Count).End(xlUp).Row
    If lr1 > 11 Then ws2.Range("C12", "H" & lr1).Clear
    If lr2 > 11 Then ws3.Range("C12", "M" & lr2).Clear
    lr3 = ws4.Range("C" & Rows.Count).End(xlUp).Row
    lr4 = ws5.Range("N" & Rows.Count).End(xlUp).Row
    ReDim sn(1 To lr3, 1 To 6)
        For Each cl In ws4.Range("C6", "C" & lr3)
            If cl.Value = Target.Value Then
                x = x + 1
                sn(x, 1) = cl
                sn(x, 2) = cl.Offset(, 6)
                sn(x, 3) = cl.Offset(, 7)
                sn(x, 4) = cl.Offset(, 8)
                sn(x, 5) = cl.Offset(, 29)
                sn(x, 6) = cl.Offset(, 31)
            End If
        Next
        If x > 0 Then
            ws2.Range("C12").Resize(x, 6) = sn
            Set rng = ws2.Range("C12", "H" & x + 11)
            rng.Sort key1:=ws2.Range("G11"), Order1:=xlDescending, Header:=xlNo
            With rng.Borders
                .LineStyle = xlContinuous
            End With
        End If
    ReDim arr(1 To lr4, 1 To 11)
        x = 0
        For Each cl In ws5.Range("N6", "N" & lr4)
            If cl.Value = Target.Value Then
                x = x + 1
                arr(x, 1) = cl
                arr(x, 2) = cl.Offset(, -5)
                arr(x, 3) = cl.Offset(, -4)
                arr(x, 4) = cl.Offset(, -3)
                arr(x, 5) = cl.Offset(, 5)
                arr(x, 6) = cl.Offset(, 6)
                arr(x, 7) = cl.Offset(, 7)
                arr(x, 8) = cl.Offset(, 8)
                arr(x, 9) = cl.Offset(, 9)
                arr(x, 10) = cl.Offset(, 10)
                arr(x, 11) = cl.Offset(, 11)
            End If
        Next
        If x > 0 Then
            ws3.Range("C12").Resize(x, 11) = arr
            Set rng = ws3.Range("C12", "M" & x + 11)
            rng.Sort key1:=ws3.Range("G11"), Order1:=xlDescending, Header:=xlNo
            With rng.Borders
                .LineStyle = xlContinuous
            End With
        End If
End If
End Sub

</code>

 

[AlphaFrog]

Try something like this. Change which column to format to suit.

ws3.Columns("C").NumberFormat = "#,##0"

 

[bluepenink]

Try something like this. Change which column to format to suit.

ws3.Columns("C").NumberFormat = "#,##0"

thanks for your reply

where would i add that line? thanks.

 

[AlphaFrog]

thanks for your reply

where would i add that line? thanks.

Right after you paste the data.

 

[bluepenink]

Right after you paste the data.

great thanks