Run Time Error 1004

[andrewb90]

Hello all,

I have this code that looks at the fill color of my cells and then assigns a gradient fill color to cells on another sheet. When I was testing this last week, it seemed to work quite well, now however I am getting an error, and I'm not sure what I could've done. Any help would be much appreciated.

Sub Reset_MasterAvail() 'This code is to copy all availability onto the scheduler after it is cleared.Dim I As Integer 
Dim j As Integer
Dim objColorStop As ColorStop
Dim mySelect As Range
Dim myOthershtRng As Range




For I = 88 To 207
    If I = 88 Or I = 107 Or I = 126 Or I = 145 Or I = 164 Or I = 193 Then 'skipped rows
        GoTo Next1
    Else
        For j = 5 To 18 Step 2 ' j is column #
            If Sheets("Master Availability").Cells(I, j).Interior.Color = RGB(255, 192, 0) And Sheets("Master Availability").Cells(I, j + 1).Interior.Color = RGB(255, 192, 0) Then
               With Sheets("Master Availability").Cells.Select
               End With
               Set myOthershtRng = Sheets("Scheduler").Cells(I, j)
                With myOthershtRng.Interior
                .Pattern = xlPatternRectangularGradient 'Gray Gradient
                .Gradient.RectangleLeft = 0.5
                .Gradient.RectangleRight = 0.5
                .Gradient.RectangleTop = 0.5
                .Gradient.RectangleBottom = 0.5
                .Gradient.ColorStops.Clear
                End With
    
                Set objColorStop = myOthershtRng.Interior.Gradient.ColorStops.Add(0)
                With objColorStop
                    .ThemeColor = xlThemeColorLight1
                    .TintAndShade = 0
                End With
    
                Set objColorStop = myOthershtRng.Interior.Gradient.ColorStops.Add(1)
                With objColorStop
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.349009674367504
                End With
            
            End If
        Next j
    End If
Next1:
Next I
End Sub

 

[Michael M]

Have you stepped through manually, to find where it errors out ??
Are any of the relavent cells locked ??

 

[andrewb90]

None of the cells are locked. When I stepped though, this line got flagged:

With Sheets("Master Availability").Cells.Select

I'm just not sure what's wrong with it...
The cells should be what cells that met the conditions in the above line of code right?

 

[andrewb90]

Ok, So I feel kinda dumb now....I rechecked my sheet, and it turns out that somehow 1 row in the middle of my range was completed protected while the rest were all unprotected. I fixed it and now it seems to work.

I appreciate your help!

 

[Michael M]

Glad to help...

 

[andrewb90]

To continue this thread:

I have a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> code that is supposed to look at the cell colors then fill in the identical cells on another sheet. My problem is that If I have two cells together, the other sheet only colors one cell. I've tried a few different variations, but I can't get consistent results.
I think I maybe overthinking the problem (which for me usually just makes things worse...) Perhaps somebody just has a very simple solution for me...
here's the code:

[/COLOR]
[FONT=Verdana]Sub R_M_Avail() [/FONT]
Dim I As Integer
Dim j As Integer
Dim objColorStop As ColorStop
Dim mySelect As Range
Dim myOthershtRng As Range


For I = 88 To 207
If I = 88 Or I = 107 Or I = 126 Or I = 145 Or I = 164 Or I = 193 Then 'skipped rows
GoTo Next1
Else
For j = 5 To 18 Step 2 ' j is column #
If Sheets("Master Availability").Cells(I, j).Interior.Color = RGB(255, 192, 0) And Sheets("Master Availability").Cells(I, j + 1).Interior.Color = RGB(255, 192, 0) Then
With Sheets("Master Availability").Cells.Select
End With
Set myOthershtRng = Sheets("Scheduler").Cells(I, j)
With myOthershtRng.Interior
.Pattern = xlPatternRectangularGradient 'Gray Gradient
.Gradient.RectangleLeft = 0.5
.Gradient.RectangleRight = 0.5
.Gradient.RectangleTop = 0.5
.Gradient.RectangleBottom = 0.5
.Gradient.ColorStops.Clear
End With

Set objColorStop = myOthershtRng.Interior.Gradient.ColorStops.Add(0)
With objColorStop
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With

Set objColorStop = myOthershtRng.Interior.Gradient.ColorStops.Add(1)
With objColorStop
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349009674367504
End With

End If
Next j
End If
Next1:
Next I [COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

 

[andrewb90]

bump^^