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

[BertyBerts]

Hi,

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

The problem is that, as the program loops through so many cells, itselects 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 thesame 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 – codebelow:

'Count Number of Green Ticks and Record
'If Green Tick in Completed Column)Green Each Process Stream DescriptionCell/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

 

[jproffer]

Almost any time there is .Select and then Selection., you can remove both and it will do the same thing......ALMOST any time.

But you're saying that this:

[FONT=Calibri][SIZE=3][COLOR=#000000]Range(StatusMarker.Offset(0, 2), StatusMarker.Offset(0, 4)).Select
Selection.Interior.ThemeColor = xlThemeColorDark2[/COLOR][/SIZE][/FONT]

works, but this:

[FONT=Calibri][SIZE=3][COLOR=#000000]Range(StatusMarker.Offset(0, 2), StatusMarker.Offset(0, 4)).Interior.ThemeColor = xlThemeColorDark2[/COLOR][/SIZE][/FONT]

doesn't?

I'm not in a place to test it right now, but I can't imagine it wouldn't work. If it bugs out, what is the error that it specifies? Or does it just run through but not do anything?

 

[BertyBerts]

Hi. Thanks for your really quick response.

It works with .interior.colour but not with with .interior.theme.colour for some reason.

Cheers, BertyBerts

 

[jproffer]

I've never used it, so I don't know but according to your first post, it should be ThemeColor...not Theme.Color...(no dot). Is that right? And could that be why it's not working? Like I said, I don't know...never used it, just noticed that difference between Post 1 and Post 3

 

[Fluff]

How about

StatusMarker.Offset(0, 6).Interior.Color = 5296274
With Range(StatusMarker.Offset(0, 2), StatusMarker.Offset(0, 4))
   .Interior.ThemeColor = xlThemeColorDark2
   .Font.ThemeColor = xlThemeColorDark2
End With