How to improve performance for a Macro checking cells out in a big range

[buhnen]

...Hi everybody,

I need some help again from a VBA Guru probably...

Iīve created this little Macro below to change the font color of some cells on a table...

Sub ResetCellFormats()
Set TargetCells = Range("C:AI,AL:GE")

If [ConditionalTextFormattingButton] = 3 Then

    For Each cell In TargetCells
    If cell.Font.ColorIndex = 26 Then cell.Font.ColorIndex = 1
    Next

End If
End Sub

The problem is, the table Iīm applying the Macro to is so big that it take ages...2248 rows !!

isnīt there another more efficient way to approach this aim with VBA Code?

As you can see in the code above, itīs only abaout changing the font color to black for the cell values in the table containing a magenta color font...

Thanks in advance for your help,
Ruben

[TMS]

Range("C:AI,AL:GE")

is not processing 2248 rows, it is processing 1,048,576 rows by a lot of columns. That's why it's slow.

[TMS]

Maybe:

Sub ResetCellFormats()
Dim TargetCells As Range, cell As Range
Set TargetCells = Range("C1:AI2248,AL1:GE2248")

If [ConditionalTextFormattingButton] = 3 Then

    For Each cell In TargetCells
        If cell.Font.ColorIndex = 26 Then cell.Font.ColorIndex = 1
    Next

End If
End Sub

[buhnen]

Hello TMS,

Thanks a lot! it has worked out great .... Now it only takes less than a minute!...I had missed this crucial fact:

is not processing 2248 rows, it is processing 1,048,576 rows by a lot of columns. That's why it's slow.

[TMS]

You're welcome.

This should make it faster:

Option Explicit

Sub ResetCellFormats()
Dim TargetCells As Range, cell As Range
Set TargetCells = Range("C1:AI2248,AL1:GE2248")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If [ConditionalTextFormattingButton] = 3 Then

    For Each cell In TargetCells
        If cell.Font.ColorIndex = 26 Then cell.Font.ColorIndex = 1
    Next

End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

[TMS]

Thanks for the rep.