Excel VBA Worksheet Calculate running slowly

[mcmahobt]

Hello all,

My question pertains to the Worksheet_Calculate VBA sub. I have the code below that I wish to execute every time a cell is "confirmed" within the worksheet.

Basically, I have the table range that spans columns A:I, with various field headers. Column A has numbers that either contain integers or values with decimals in them. The current code cycles through column A, and if the cell value contains a decimal, it is formatted as white fill with black bordering and font. If it does not contain a decimal, it is formatted as blue fill with white font, etc.

The current code works, but is very slow to run. I am hoping for some guidance on getting a more consolidated/streamlined block of code for this...something that may include a "For Each Cell in Rng" loop?

Any help is appreciated!

Private Sub Worksheet_Calculate()

Application.ScreenUpdating = False


Dim ws As Worksheet, Rng As Range, LR As Integer, Lc As Integer, Cell As Range, r As Integer
Set ws = Application.ActiveWorkbook.Worksheets("Sheet1"): LR = ws.Range("A" & Rows.Count).End(xlUp).row:
Set Rng = ws.Range(Cells(4, 1), Cells(LR, 1))


For r = 4 To LR
    If InStr(Cells(r, 1).Value, ".") <> 0 Then
        With ws.Range(Cells(r, 1), Cells(r, 9))
            .Borders.LineStyle = xlContinuous
            .Borders.Weight = xlThin
            .Borders.ColorIndex = xlAutomatic
            .Interior.Color = RGB(255, 255, 255)
            .Font.Color = vbBlack
        End With
    Else
        With Range(Cells(r, 1), Cells(r, 9))
            .Borders.LineStyle = xlContinuous
            .Borders.Weight = xlThin
            .Borders.ColorIndex = xlAutomatic
            .Interior.Color = RGB(31, 73, 125)
            .Font.Color = vbWhite
        End With
    End If
Next r


Application.ScreenUpdating = True


End Sub

 

[SpillerBD]

Currently, how many rows are you running?

 

[mcmahobt]

Variable LR goes through the last active cell in column A, so that is currently row 186.

 

[imag1ne]

Is there a reason why you go trough all cells, not only the one that is changed?

Because if you run this code on cell change ("Worksheet_Change") and target the active cell it will execute the code only there and would run a lot quicker.

 

[mcmahobt]

Is there a reason why you go trough all cells, not only the one that is changed?

Because if you run this code on cell change ("Worksheet_Change") and target the active cell it will execute the code only there and would run a lot quicker.

I was hoping to do just that at first, but the integer/decimal values are calculated through a formula which changes based on entries that can occur when rows are inserted into the table, so I need the entire table to auto-update on the change event, not just the last row.

 

[RoryA]

Please read the forum rules on cross-posting and comply with them in future. Thanks.

 

[imag1ne]

I was hoping to do just that at first, but the integer/decimal values are calculated through a formula which changes based on entries that can occur when rows are inserted into the table, so I need the entire table to auto-update on the change event, not just the last row.

Try running it manually not from "Calculate" event.
If then it runs a lot quicker, then I would suggest to running it from a button.
Because I got a feeling that piece of code is run 186 times even if you just change 1 cell, as it loops trough the range and each cell change in turn triggers another calculation event.

But I might be wrong.


EDIT:
Just did a little test and it did run the code multiple times as the sheet recalculated it self every time a single cell was changed.