VBA Slow Peformance on Formatting

A

Anson

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?
 
F

Frank Kabel

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
 
A

Anson

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
 
Top