VBA Slow Peformance on Formatting
 

I am using Excel 97 and have been trying to use macro to do formatting on certain cells based on the value (Conditional Formatting doesn't work as I have more then 5 conditions). The program works fine but it takes way too long to do it. For example, a procedure that completes in 10 second would take 50 seconds. Anyone knows why it takes this long? And how to speed it up?

VBA Slow Peformance on Formatting
 

Hi
without seeing your macro as one idea:
disable screenupdating during the macro execution. Also emove select
statements.

But you may post the relevant part of your current macro


--
Regards
Frank Kabel
Frankfurt, Germany


Anson wrote:
I am using Excel 97 and have been trying to use macro to do
formatting on certain cells based on the value (Conditional
Formatting doesn't work as I have more then 5 conditions). The
program works fine but it takes way too long to do it. For example, a
procedure that completes in 10 second would take 50 seconds. Anyone
knows why it takes this long? And how to speed it up?

VBA Slow Peformance on Formatting
 

Thanks for responding the codes are below. Initally I had:
".Borders(xlEdgeBottom).Weight = xlHairline" coded in each of the cases in Select Case but it showed down the program too much to be acceptable.


Private Sub Refresh(iStart As Integer)

Dim i As Integer
Dim Pivot As Range, rngCurrent As Range
Dim colNumber As Integer, rowNumber As Integer

ActiveSheet.Unprotect password:="anson"

'----------------------------------------------------------
'Gather Sheet Information
Set Pivot = [A12]
colNumber = Range(Pivot, Pivot.End(xlToRight)).Count
rowNumber = Range(Pivot.Offset(1, 0), Pivot.End(xlDown)).Count

'----------------------------------------------------------
'Reset Universal Formatting
With Range(Pivot.Offset(1, 0), Pivot.Offset(rowNumber, colNumber - 1))
.Borders(xlEdgeBottom).Weight = xlHairline
.Borders(xlInsideHorizontal).Weight = xlHairline
.Font.Name = "MS Sans Serif"
.Font.ColorIndex = xlAutomatic
End With

'Reset Individual Formatting
For i = 1 To colNumber
Select Case Pivot.Offset(0, i - 1).Value

Case "Confirm Merit Eligibility (Yes/No)"
Range(Pivot.Offset(1, i - 1), Pivot.Offset(rowNumber, i - 1)). _
Interior.ColorIndex = 35

Case "New Perf. Rating"
With Range(Pivot.Offset(1, i - 1), Pivot.Offset(rowNumber, i - 1))
.Interior.ColorIndex = 35

.Validation.Delete
.Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Exceeds,Meets,Below"
.Validation.IgnoreBlank = True
.Validation.InCellDropdown = True
.Validation.ShowInput = True
.Validation.ShowError = True
End With

Case "Comments"
Range(Pivot.Offset(1, i - 1), Pivot.Offset(rowNumber, i - 1)). _
Interior.ColorIndex = 35

Case "% Salary Increase"
Range(Pivot.Offset(1, i - 1), Pivot.Offset(rowNumber, i - 1)). _
Interior.ColorIndex = 35

Case "$ Salary Increase"
Range(Pivot.Offset(1, i - 1), Pivot.Offset(rowNumber, i - 1)). _
Interior.ColorIndex = 35

Case "% Merit Bonus"
Range(Pivot.Offset(1, i - 1), Pivot.Offset(rowNumber, i - 1)). _
Interior.ColorIndex = 35

Case "$ Merit Bonus"
Range(Pivot.Offset(1, i - 1), Pivot.Offset(rowNumber, i - 1)). _
Interior.ColorIndex = 35

Case "Optional Field"
Range(Pivot.Offset(1, i - 1), Pivot.Offset(rowNumber, i - 1)). _
Interior.ColorIndex = 35

End Select
Next i

ActiveSheet.Protect password:="anson"


End Sub


"Frank Kabel" wrote:

Hi
without seeing your macro as one idea:
disable screenupdating during the macro execution. Also emove select
statements.

But you may post the relevant part of your current macro


--
Regards
Frank Kabel
Frankfurt, Germany


Anson wrote:
I am using Excel 97 and have been trying to use macro to do
formatting on certain cells based on the value (Conditional
Formatting doesn't work as I have more then 5 conditions). The
program works fine but it takes way too long to do it. For example, a
procedure that completes in 10 second would take 50 seconds. Anyone
knows why it takes this long? And how to speed it up?