Speed Imporvements

[Djblois]

Anybody know a quicker way to perform this:
For i = 2 To finalRow
If Range("M" & i) <> Range("N" & i) Then
With Range("N" & i)
.Font.ColorIndex = 5
.Font.Bold = True
End With
End If
Next i

My goal it to check the N column and if the cell is different then the M column than it will hightlight it.


Also, anybody know which is quicker:

Range("G2:G" & finalRow).Value = Range("G2:G" & finalRow).Value

or

Columns("P:P").TextToColumns Destination:=Range("P1")

there isn't much a difference in speed but I need every second if possible.

Finally, any way to make this one quicker:

'Add Percentage to End (T)
Range("U1").FormulaR1C1 = "%"
Range("U2:U" & finalRow).FormulaR1C1 = "=IF(RC[-4]=0,""No Sale"",RC[-1]/RC[-4])"
Range("U1:U" & finalRow).Value = Range("U1:U" & finalRow).Value


With Range("U2", Range("U2").End(xlDown).Offset(-1, 0))
With .FormatConditions.Add(xlCellValue, xlLess, "0")
.Font.Bold = True
End With
With .FormatConditions.Add(xlCellValue, xlEqual, "=""No Sale""")
.Font.Bold = True
.Interior.ColorIndex = 8
End With
With .FormatConditions.Add(xlCellValue, xlLess, Profit)
.Font.Bold = True
.Interior.ColorIndex = 6
End With
.NumberFormat = "#,##0.00%;[Red]-#,##0.00%"
End With

I am adding conditional formating to the last column which is always different in size.

[Bob Phillips]

Anybody know a quicker way to perform this:
For i = 2 To finalRow
If Range("M" & i) <> Range("N" & i) Then
With Range("N" & i)
.Font.ColorIndex = 5
.Font.Bold = True
End With
End If
Next i
My goal it to check the N column and if the cell is different then the M column than it will hightlight it.[quote]

When iterating through data, always best to switch off screenupdating, and set calculation to manual, and reset at the end.

But this is far quicker.



With Range("N2").Resize(finalrow - 1)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC<>RC[-1]"
With .FormatConditions(1)
.Font.ColorIndex = 5
.Font.Bold = True
End With
End With


[quote=Djblois]
Also, anybody know which is quicker:

Range("G2:G" & finalRow).Value = Range("G2:G" & finalRow).Value
or

Columns("P:P").TextToColumns Destination:=Range("P1")
there isn't much a difference in speed but I need every second if possible.

Time it and see. I would expect it to be the latter,


Finally, any way to make this one quicker:

'Add Percentage to End (T)
Range("U1").FormulaR1C1 = "%"
Range("U2:U" & finalRow).FormulaR1C1 = "=IF(RC[-4]=0,""No Sale"",RC[-1]/RC[-4])"
Range("U1:U" & finalRow).Value = Range("U1:U" & finalRow).Value


With Range("U2", Range("U2").End(xlDown).Offset(-1, 0))
With .FormatConditions.Add(xlCellValue, xlLess, "0")
.Font.Bold = True
End With
With .FormatConditions.Add(xlCellValue, xlEqual, "=""No Sale""")
.Font.Bold = True
.Interior.ColorIndex = 8
End With
With .FormatConditions.Add(xlCellValue, xlLess, Profit)
.Font.Bold = True
.Interior.ColorIndex = 6
End With
.NumberFormat = "#,##0.00%;[Red]-#,##0.00%"
End With
I am adding conditional formating to the last column which is always different in size.

Unlikely, there is nothing to get at in there.

[Djblois]

XLD,

THank you that worked and I am trying to test the other code to see which is quicker. How about these:

For i = 1 To finalRow
If Cells(i, "A") <> "Sales" Then
Range("J" & i).Resize(, 2).Insert shift:=xlToRight
End If
Next

What that does is if the A column is not Sales then it moves the j column over by 2

Also, sometimes when the data is large this locks up my computer:

Cells(1, 1).Resize(finalRow, finalColumn).EntireColumn.AutoFit

[Bob Phillips]

In that final bit of code, there is no need to resize the rows, as you are doing entirecolum.

I would use



Columns(1).Resize(,finalColumn).Autofit