Excel Not Responding

[PWD]

Good afternoon, I'm just trying to start something really simple but, using this macro, Excel stops responding for the longest time. I'm fairly confident that it's not the work's network, although that could never be discounted, and I just can't see that there's anything here that would cause anything to have a fit!! It does sort of hesitate a bit on the colouring bit when I F8 through it. Is there any better way to achieve cell colouring?

Sub My_Sams_PTL_Colouring()
'
Dim RowNum As Long
Dim a As Long
Dim x As Long

     RowNum = Range("D2").End(xlDown).Row 'Last row of existing used rows
     
            For a = 3 To RowNum 'Loop through the rows) to find a match on rows with TrackingNotes
            If Range("S" & a) <> "" Then
            
            Range("D" & a).Interior.Color = RGB(252, 213, 180) 'Fleshy, pinky Orange
            Range("S" & a).Interior.Color = RGB(252, 213, 180) 'Fleshy, pinky Orange
            End If
            Next a

End Sub

Many thanks,
D€$

 

[strongm]

Without offering a direct solution to your issue, would this not be better dealt with by using conditional formatting?

 

[PWD]

Hi strongm, well, there will be about 5 different conditions for shading the cells so I thought I'd just build up several processes in code. I presume that you are talking about just shading from within Excel?

Many thanks,
D€$

 

[combo]

RowNum = Range("D2").End(xlDown).Row 'Last row of existing used rows

No, it's a row of a cell selected after selecting D2 and pressing CTRL+DOWN. For finding last row you may use UsedRange, for active sheet:
[tt]RowNum = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1[/tt]
however formatted cells are in used range too.

You may try switch off screen updating before starting formatting to speed up the code: [tt]Application.ScreenUpdating=False[/tt]

combo

 

[strongm]

>here will be about 5 different conditions for shading the cells

Not a problem for conditional formatting

>I presume that you are talking about just shading from within Excel?

Conditional formatting is somewhat more than that