How to avoid cell SELECTION but still format cells to speed up the macro running speed

Hi,

I have the following code that checks for a condition in the range, and if it is a ‘1’ then it select some cells and formats them (by changing the interior colour, interior.ThemeColor and Font.ThemeColor).

The problem is that, as the program loops through so many cells, it selects many – prior to making the changes – and the macro is very very slow.

I’m looking either (1) for a way to avoid the cell selection* but use the same basic principle or (2) alternative way all together.

* I’ve found that I can do StatusMarker.Offset(0, 6).Interior.Color = 5296274 but this method doesn’t work with the ThemeColour requirements.

The macro’s far too slow and any help will be really appreciated – code below:

'Count Number of Green Ticks and Record
'If Green Tick in Completed Column)Green Each Process Stream Description Cell/Grey Time Columns
'1st Process Stream
'Set Counters
y = 0
z = 0
For Each StatusMarker In StatusTimeSpan
If StatusMarker.Offset(0, 5) = 1 Then
y = y + 1 'MsgBox x
StatusMarker.Offset(0, 6).Select
Selection.Interior.Color = 5296274
Range(StatusMarker.Offset(0, 2), StatusMarker.Offset(0, 4)).Select
Selection.Interior.ThemeColor = xlThemeColorDark2
Selection.Font.ThemeColor = xlThemeColorDark2
End If
Next StatusMarker 'MsgBox x
Sheets("Data").Range("B8").Value = y

please always use code tags (#-icon) to make it more easier to read

in essence that is easy the do..
after each .select you see a next line with selection. combine the two lines by removing both the select an selection part

Please Login or Register  to view this content.

The last part you could also use an with.. end with. This allows you to manipulate multiple properties for specific range

Please Login or Register  to view this content.