Soooo Sloooow!

[primaryteach]

Hi,

I am helping the local school sports partnership to create a spreadsheet of what acitivities the children do. It nearly does all the things I want it to do, but it goes so slow every time a user enters one piece of data into worksheet. Initially I thought it was just the size of the file (15000 rows and lots of columns). However, having seen other spreadsheets with over 20mb of data running smoothly on older PCs I guess there must be more to it than that.

So I would be really grateful if you could take a look at the file attached. Could it be my use of validated data drop-down lists? Is it any of the VBA coding? The use of filters? I have removed some conditional formatting colour coding (e.g. column G) but this does not seem to resolve the speed issue. I have also added a 'Data entry mode' button, which runs a calculationmanual vba code, but this seems to make no difference to the overall speed.

I've had to compress the file to .zip format as it is over 4mb. The password is 'password' to unprotect the sheets.

Any suggestions will very helpful.

Simon
UK Teacher

[Simon Lloyd]

i Haven't looked at your file but i suggest you turn the calculation to manual, once all data is entered the user can then hit F9 and the worksheet will calculate.

[Simon Lloyd]

Apart from all the selecting you do which isn't needed, you have this problem!
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Me.Unprotect Password:="password"
For Each cel In Range("G11:G11000").Cells
If IsNumeric(cel.Value) And cel.Value <> "" Then
If cel.Value >= 0 And cel.Value < 180 Then
cel.Interior.ColorIndex = 3
ElseIf cel.Value >= 180 Then
cel.Interior.ColorIndex = 10
Else 'default conditions
cel.Interior.ColorIndex = 0
cel.Font.ColorIndex = 1
End If
Else
cel.Interior.ColorIndex = 0
cel.Font.ColorIndex = 1
End If
Next
Me.Protect Password:="password"
End Sub[/VBA]Everytime you change a cell you are running through this code 11,000 cells?
Why not:[VBA]For Each cel In Range("G11:G" & Range("G" & rows.count).end(xlup).row)[/VBA]Then it will work on the rows that have data in them, but do you really need to perform this action everytime a cell is changed?

[mdmackillop]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Calculate
Me.Unprotect Password:="password"
With Cells(Target.Row, "G")
If IsNumeric(.Value) And .Value <> "" Then
If .Value >= 0 And .Value < 180 Then
.Interior.ColorIndex = 3
ElseIf .Value >= 180 Then
.Interior.ColorIndex = 10
Else 'default conditions
.Interior.ColorIndex = 0
.Font.ColorIndex = 1
End If
Else
.Interior.ColorIndex = 0
.Font.ColorIndex = 1
End If
End With
Me.Protect Password:="password"

End Sub[/VBA]