Excessively slow script (Update wanted)

[carrellim]

[VBA]S
Sub ChangeTest()
'These are your variables
'Do not change these

Dim ArchiveOld
Dim ArchiveNew
Dim lCount As Long

'This is where the script starts

Range("E6").Select

'This is your timer

lCount = 0

'this is the amount of times the script will run
'Change number to the amount of cells

Do Until lCount = 225


'Set Variables to cells and then
'compare them to each other

Set ArchiveNew = ActiveCell
ActiveCell.Offset(0, 1).Select
Set ArchiveOld = ActiveCell

If ArchiveNew = ArchiveOld Then
ArchiveNew.Interior.ColorIndex = 10
Else
ArchiveNew.Interior.ColorIndex = 6
End If

ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select


'Add 1 to your counter
'This should reloop lcount-1 amount of times

lCount = lCount + 1

Loop

'This brings your pointer back up to the top of the screen

Range("A1").Select
End Sub
[/VBA]

[Kenneth Hobs]

It is better to use a For() or For Each routine to loop. You could use the Range() with Offset(). Select is usually the slowest method.

You can use or get ideas to speed up at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=1035

[carrellim]

Yeah that would probably help.
What about getting around the .Select routine?
I wrote this myself.. I don't know any other function or class that will do this type of work for me..

Any other comments would be appreciated.

[carrellim]

Updated to the For() loop. No results.
[VBA]Sub ChangeTest()
'These are your variables
'Do not change these
Dim ArchiveOld
Dim ArchiveNew
Dim lCount As Long

'This is where the script starts

Range("E6").Select

'This is your timer

'this is the amount of times the script will run
'Change number to the amount of cells

For lCount = 0 To 10


'Set Variables to cells and then
'compare them to each other

Set ArchiveNew = ActiveCell
ActiveCell.Offset(0, 1).Select
Set ArchiveOld = ActiveCell

If ArchiveNew = ArchiveOld Then
ArchiveNew.Interior.ColorIndex = 10
Else
ArchiveNew.Interior.ColorIndex = 6
End If

ActiveCell.Offset(1, -1).Select


'Add 1 to your counter
'This should reloop lcount-1 amount of times
Next lCount


'This brings your pointer back up to the top of the screen

Range("A1").Select
End Sub[/VBA]

More updates needed

[ntrauger]

Much better. [vba]Sub ChangeTest()
'These are your variables
'Do not change these
'The fewer variables, the less strain on your system memory.
'Neglegible in this situation, but good practice nonetheless.

Dim lCount As Long

'This is where the script starts
'Avoid the .Select method except for how it is used at the end of this procedure.

'Range("E6").Select

'Defer updating the display until finished making changes.
'Screen updating is a big drain on speed.

Application.ScreenUpdating = False

'This is your timer

'this is the amount of times the script will run
'Change number to the amount of cells

For lCount = 0 To 10

'Compare cells systematically, using the counter variable
'to increment down the sheet.

If Cells(6 + lCount, 5) = Cells(6 + lCount, 6) Then
Cells(6 + lCount, 5).Interior.ColorIndex = 10
Else
Cells(6 + lCount, 5).Interior.ColorIndex = 6
End If

Next lCount

'This ensures your pointer is at the top of the screen

Range("A1").Select

'Update your display

Application.ScreenUpdating = True
End Sub[/vba]

[carrellim]

Yes, speed has increase!!
Nate would you have any suggestions for doing this in excel other than
Comparing each one individually?
I had to write this myself because I don't know of a function that does it.

- Mike

[ntrauger]

Conditional formatting would be a much better approach.

[carrellim]

I agree but the amount of cells being updated needs changed every once and a while an conditional formating in excel 2003 only does one cell at a time..

I wish there was a way to do an array..

[ntrauger]

Not sure what you mean exactly, but if you create the conditional formatting for the first cell and remove the "$" from the cell addresses, you can copy and paste or fill the formatting down the whole column.

[carrellim]

excel 2003 my friend

[ntrauger]

Same here.

In E5, set conditional formatting rules as follows.

Condition 1: Cell Value Is...equal to..."=F5" (Green fill)
Condition 2: Cell Value Is...not equal to..."=F5" (Yellow fill)

Hit okay, and drag the fill handle down to cover the rest of the cells you are concerned with in column E.