performance tuning of vba macro

Hello everyone
Iam very new to macro, trying to learn the stuff through online .

I have been trying to make changes to already existing macros , I was successful in making some changes but Iam having issues with performance .

The macro works well for few rows but it just freezes when I run for large volume ( say 1600 rows) . I think it should not be a issue for 1600 , may be my macro is stuck in a loop and not able to come out .

The macro looks for the row "bal" and updates the formula in subsequent columns and also does some formatting in the rows "soo" . My main issue is ,it works fine for few rows but when rows increases it freezes . I wanna know is there any command where it updates formula in column range at a time
I mean instead of looping through each column ( as per below macro) can it set that formula in all columns at a time. below is the part of macro where it loops for updating each column . I really appreciate any help i can get .


For j = 11 To 50
'Set each subsequent cell with this formulae
Cells(i, j).FormulaR1C1 = "=IF(RC[-1]+(R[-1]C-R[-2]C)<=0,0,RC[-1]+(R[-1]C-R[-2]C))"

Next j
############################################################
Below is the complete macro.

Sub Nat_View_VP()
Sheets("National Impact (VP)").Select
'Find the first cell in the row
Range("j2").Select
'Loop
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 9).Value = "Bal" Then
'Formula for first column
Cells(i, 10).FormulaR1C1 = "=IF(R[-2]C[-3]+(R[-1]C-R[-2]C)<=0,0,R[-2]C[-3]+(R[-1]C-R[-2]C))"
'Loop to go through each column
For j = 11 To 50
'Set each subsequent cell with this formulae
Cells(i, j).FormulaR1C1 = "=IF(RC[-1]+(R[-1]C-R[-2]C)<=0,0,RC[-1]+(R[-1]C-R[-2]C))"
'Colour cell based on its value
'Cells(i, j).NumberFormat = "#,##0_ ;[Red]-#,##0 "
Cells(i, j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=0"
Cells(i, j).FormatConditions(Cells(i, j).FormatConditions.Count).SetFirstPriority
With Cells(i, j).FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -16383844
.TintAndShade = 0
End With
With Cells(i, j).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
End With
Cells(i, j).FormatConditions(1).StopIfTrue = False

Next j
End If
If Cells(i, 9).Value = "Fcst" Then
'Find the cell that is the lead time and colour it Orange.
Cells(i, 9).Offset(0, Cells(i, 6)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = 2
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
End If
If Cells(i, 9).Value = "SOO" Then
'Make all the cells have a conditional fill of blue.
For j = 10 To 50
Cells(i, j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=0"
Cells(i, j).FormatConditions(Cells(i, j).FormatConditions.Count).SetFirstPriority
With Cells(i, j).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 16737843
.TintAndShade = 0
End With
Cells(i, j).FormatConditions(1).StopIfTrue = False
Next j
End If
Next i

End Sub

It's hard to help you with just the information that you have supplied. It might help if you attach the file.

Thanks for the reply . Ya it is better to attach sheet . Iam new to forum, please can you advise how to attach my file , i do not see that option in reply window .

You have to hit the 'Go Advanced' button (bottom right).
Then, toward the bottom (below the text entry window), you will see (in Bold) the word: Attachments.
To the right of that, you will see a hyperlink: Manage Attachments. This will lead to another page where you can
browse, attach your file and then upload it. Then, submit your reply. This will send the file.

It's good to read the Forum Rules from top to bottom. It will help w/ the overall communication back & forth.
You will see a button at the top labeled Forum Rules. Hope this helps!

Thanks for the reply . I have attached the sheet with macro in it .

Cannot find the macro in the file

The file extension may be wrong?

Roops218, The file extension must be 'xlsm' to allow it to contain a macro. A 'xlsx' file can not be saved with an intact macro in it. Save it again, with the macro, as an xlsm and attach & re-post. Thanks!