Interior.Color Issues

[Pestomania]

Hi. So I am using a userform to determine the color of a sheet. But it keeps having issues getting past one sheet. I'll show the code below.

Sub ColorAllSheets ()

Dim wsd as worksheet
Application.DisplayAlerts = False
For each wsd in worksheets  If left(wsd.name,4) = "asse" or left(wsd.name,4)="ASSE" then
Wsd.select

Eun "Userform_Initialize"

End if
Next WSD

End sub

At this point a user form pops up with four buttons "SMALL, MEDIUM, LARGE, NONE"

When the user selects a button it will color a specific range of cells.
An example:

Sub ColorYellow()
Range("T13:U21").Select
   With Selection.Interior
        .Color = RGB(255,255,0)
        .TintAndShade = 0
   End With
End Sub

It works for one sheet but when it goes to the second it says that the "RunTime Error 1004" "Application-Defined or object defined erroe" and it highlights .Color = RGB.

I don't know why it works once but not twice or third.

 

[Puertorekinsam]

I made a few small changes to the code when doing a test, and it worked for me....

first I used userform.show instead of what I assume should have been "Run "Userform_Initialize"

also, I used a Lcase to cover all versions of the first four letters such as "Asse", "ASSE", "asse", or "AsSe"

Sub ColorAllSheets()

Dim wsd As Worksheet
Application.DisplayAlerts = False
For Each wsd In Worksheets
If LCase(Left(wsd.Name, 4)) = "asse" Then
wsd.Select

UserForm1.Show

End If
Next wsd

End Sub

In the Userform I only tested with one button but this was everything I used:

Private Sub CommandButton1_Click()
ColorYellow
Me.Hide
End Sub

Sub ColorYellow()
Range("T13:U21").Select
   With Selection.Interior
        .Color = RGB(255, 255, 0)
        .TintAndShade = 0
   End With
End Sub

and it seemed to work fine,

 

[My Aswer Is This]

So you only have one worksheet the script is selecting?
Is that true?

When I test your script it works but only if you have one sheet with the first four character that meet your requirements.

And why do you need the Userform to perform this task.

 

[shg]

Private Sub CommandButton1_Click()
  Range("T13:U21").Interior.Color = vbYellow
  Me.Hide
End Sub

 

[My Aswer Is This]

Now that I look closer it is working.

But there is surely a easier way to do this:

Do you really need a Userform?

 

[My Aswer Is This]

Why would one script just like this not work.
No need for a UserForn

Sub Color_My_Ranges()
'Modified  10/23/2018  4:44:37 PM  EDT
Dim wsd As Worksheet
Application.ScreenUpdating = False
    For Each wsd In Worksheets
        If LCase(Left(wsd.Name, 4)) = "asse" Then
            With wsd.Range("T13:U21").Interior
                .Color = RGB(255, 255, 0)
                .TintAndShade = 0
            End With
        End If
    Next wsd
Application.ScreenUpdating = True
End Sub

 

[Pestomania]

So you only have one worksheet the script is selecting?
Is that true?

When I test your script it works but only if you have one sheet with the first four character that meet your requirements.

And why do you need the Userform to perform this task.

Hello. The workbook can have 1 where or 10 or 50. No set amount. And the user needs a way to color each individual sheet, which is why I did the userform.

 

[Pestomania]

Hello. There are other color options, I only inputted one. With the userform they would select one of the four options.

 

[Pestomania]

Hi everyone. So I wanted to state some clarification.

There are 4 color options on the userform, I only posted one. Also, there are multiple sheets that meet the criteria. The left function cycles through the sheets perfectly!

But after selecting one color option and the program goes to the second worksheet, the color code crashes.

 

[My Aswer Is This]

I now understand what your wanting to do.
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.