Hi all, I have successfully created a sub that looks for 7 particular colors and add a pattern style to those cells. The problem I have with what I have created is that it is slow... not crazy slow but slow enough to make the end user think that the program has frozen. Any help would be huge!
Sub REcolorer()
Application.ScreenUpdating = False
Dim cl As Range
Dim active_rng As Range
Set active_rng = Range("A2:MM1200")
For Each cl In active_rng
If cl.Interior.Color = RGB(204, 255, 255) Then
With cl.Interior
.Pattern = xlGray8
.PatternThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = -0.499984740745262
End With
End If
Next cl
Application.ScreenUpdating = True
For Each cl In active_rng
If cl.Interior.Color = RGB(204, 255, 204) Then
With cl.Interior
.Pattern = xlGray8
.PatternThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = -0.499984740745262
End With
End If
Next cl
For Each cl In active_rng
If cl.Interior.Color = RGB(255, 255, 153) Then
With cl.Interior
.Pattern = xlGray8
.PatternThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = -0.499984740745262
End With
End If
Next cl
For Each cl In active_rng
If cl.Interior.Color = RGB(153, 204, 255) Then
With cl.Interior
.Pattern = xlGray8
.PatternThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = -0.499984740745262
End With
End If
Next cl
For Each cl In active_rng
If cl.Interior.Color = RGB(255, 153, 204) Then
With cl.Interior
.Pattern = xlGray8
.PatternThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = -0.499984740745262
End With
End If
Next cl
For Each cl In active_rng
If cl.Interior.Color = RGB(204, 153, 255) Then
With cl.Interior
.Pattern = xlGray8
.PatternThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = -0.499984740745262
End With
End If
Next cl
For Each cl In active_rng
If cl.Interior.Color = RGB(255, 204, 153) Then
With cl.Interior
.Pattern = xlGray8
.PatternThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = -0.499984740745262
End With
End If
Next cl
End Sub
Thank you!
Alex
Bookmarks