Please Help! Run-Time error 1004; Method range of object worksheet failed.

D

dsheard2015

Board Regular
Joined
May 10, 2016
Messages
134
Hello, I am in the process of transferring a lot of the sheet calculations and conditional formatting into vba for efficiency and in the process I am receiving an error that I cannot seem to figure out, "run-time error 1004; method range of object worksheet failed". I have attached all the worksheet code for this sheet, the error is coming from the worksheet change event code and the line in red text is where the error is according to the error message. I will also try to attach an image of the worksheet so you can see the layout. Any help is greatly appreciated.

Thank you very much!

VBA Code: 
Private Sub Worksheet_Activate()

Dim rLockable As Range
Dim x As Range
Dim y As Range
Dim z As Range
Dim c As Range
'Dim cell As Range
'Dim cl As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim aer As AllowEditRange
Dim sh As Worksheet

On Error Resume Next
On Error GoTo 0

Set sh = ThisWorkbook.ActiveSheet
Set rng1 = Range("M12:V50, AI15:AR50, M65:V103, AI68:AR103, M118:V156, AI121:AR156, M171:V209, AI174:AR209, M224:V262, AI227:AR262, M277:V315, AI280:AR315, M330:V368, AI333:AR368, M383:V421, AI386:AR421, M436:V474, AI439:AR474")
Set rng2 = Range("B15:B50, B68:B103, B121:B156, B174:B209, B227:B262, B280:B315, B333:B368, B386:B421, B439:B474, X15:X50, X68:X103, X121:X156, X174:X209, X227:X262, X280:X315, X333:X368, X386:X421, X439:X474")
Set rng3 = Range("B15:B50, B68:B103, B121:B156, B174:B209, B227:B262, B280:B315, B333:B368, B386:B421, B439:B474")
Set rng4 = Range("X15:X50, X68:X103, X121:X156, X174:X209, X227:X262, X280:X315, X333:X368, X386:X421, X439:X474")
Set x = Range("BC:BW")
Set y = Range("BY:CS")
Set z = Range("W:AR")

StopCalc

Unprotect Password:=Sheet577.Range("H27").Value

For Each c In rng2
    If c.Value <> Empty Then
        c.Interior.Color = RGB(221, 217, 196)
        c.Font.Color = RGB(0, 0, 0)
        c.Font.Bold = False
        Else
            c.Interior.Color = RGB(255, 255, 255)
            c.Font.Color = RGB(0, 0, 0)
    End If
Next c

For Each c In rng3
        If Range("WS15").Value = True And Range("WT15").Value = False Or Range("WS15").Value = True And Range("WT15").Value = True And Range("WW15").Value > 0 Then
            Range("B15").Interior.Color = RGB(0, 0, 0)
            Range("B15").Font.Color = RGB(255, 0, 0)
            Range("B15").Font.Bold = True
                If Range("RP15").Value = "w" Then
                    Range("B15").Interior.Color = RGB(255, 255, 255)
                    Range("B15").Font.Color = RGB(0, 0, 0)
                    Range("B15").Font.Bold = False
                End If
        End If
Next c

If Range("X15").Value > 0 Then
z.EntireColumn.Hidden = False
Shapes("Form7LogoB").Visible = True
Else: z.EntireColumn.Hidden = True
Shapes("Form7LogoB").Visible = msoFalse
End If

If Range("BY15").Value > 0 Then
y.EntireColumn.Hidden = False
Shapes("Form7LogoD").Visible = True
Shapes("UnsatTaskMsg").Visible = True
Else: y.EntireColumn.Hidden = True
Shapes("Form7LogoD").Visible = msoFalse
Shapes("UnsatTaskMsg").Visible = msoFalse
End If

If Range("BC15").Value > 0 Then
x.EntireColumn.Hidden = False
Shapes("Form7LogoC").Visible = True
Shapes("UnsatTaskMsg").Visible = True
Else: x.EntireColumn.Hidden = True
Shapes("Form7LogoC").Visible = msoFalse
Shapes("UnsatTaskMsg").Visible = msoFalse
End If

For Each aer In Protection.AllowEditRanges
    aer.Delete
Next aer

    Protection.AllowEditRanges.Add Title:="Grade Info", Range:=Range("M12:V50,AI15:AR50,M65:V103,AI68:AR103,M118:V156,AI121:AR156,M171:V209,AI174:AR209,M224:V262,AI227:AR262,M277:V315,AI280:AR315,M330:V368,AI333:AR368,M383:V421,AI386:AR421,M436:V474,AI439:AR474"), Password:=Sheets("OVERRIDE PASSWORD").Range("G14").Value

For Each c In rng1
    If Sheet278.Range("AJ1").Value = 1 Then
        c.Locked = False
            ElseIf Sheet278.Range("AJ1").Value = 2 Then
                c.Locked = True
                    ElseIf Sheet278.Range("AJ1").Value = 3 And c.Value = "" Then
                        c.Locked = False
                            ElseIf Sheet278.Range("AJ1").Value = 4 Then
                                c.Locked = True
                                    ElseIf Sheet278.Range("AJ1").Value = 5 Then
                                        c.Locked = True
                                            Else
                                                c.Locked = True
    End If
Next c
       
Range("C930").Value = 1
      
Protect Password:=Sheet577.Range("H27").Value
ResetCalc
Range("A1").Select
Range(Range("RT7").Value).Select

If Range("A2").Value = True Then
    CarryOn = MsgBox("Is there any ammo or rockets that need to be entered for this training flight?", vbYesNo, "Ammo & Rockets")
    If CarryOn = vbYes Then
    Rockets_Bullets1.Show
        If CarryOn = vbNo Then
        GoTo Cancel
        End If
    End If
End If

StopCalc

Unprotect Password:=Sheet577.Range("H27").Value
Range("XH23").Value = Range("XJ21").Value
Range("XH28").Value = Range("XJ26").Value
Range("XI31").Value = Range("XJ31").Value
Range("WU15:WU50").Value = Range("CW15:CW50").Value
Range("WU68:WU103").Value = Range("CW15:CW50").Value
Range("WU121:WU156").Value = Range("CW15:CW50").Value
Range("WU174:WU209").Value = Range("CW15:CW50").Value
Range("WU227:WU262").Value = Range("CW15:CW50").Value
Range("WU280:WU315").Value = Range("CW15:CW50").Value
Range("WU333:WU368").Value = Range("CW15:CW50").Value
Range("WU386:WU421").Value = Range("CW15:CW50").Value
Range("WU439:WU474").Value = Range("CW15:CW50").Value
Protect Password:=Sheet577.Range("H27").Value
ResetCalc

Cancel:

Protect Password:=Sheet577.Range("H27").Value

End Sub

[COLOR=rgb(147, 101, 184)]

Private Sub Worksheet_Change(ByVal c As Range)

Dim rng1 As Range 'page 1 grades
Dim rng2 As Range 'page 2 grades
Dim rng3 As Range 'previous page 1 grades
Dim rng4 As Range 'previous page 2 grades
Dim rng5 As Range 'page 1 task numbers
Dim rng6 As Range 'page 2 task numbers
Dim rng7 As Range 'WS column
Dim rng8 As Range 'WT column
Dim rng9 As Range 'WW column


Set rng1 = Range("M15:V50,M68:V103,M121:V156,M174:V209,M227:V262,M280:V315,M333:V368,M386:V421,M439:V474")
Set rng2 = Range("AI15:AR50,AI68:AR103,AI121:AR156,AI174:AR209,AI227:AR262,AI280:AR315,AI333:AR368,AI386:AR421,AI439:AR474")
Set rng3 = Range("BN15:BW50,BN68:BW103,BN121:BW156,BN174:BW209,BN227:BW262,BN280:BW315,BN333:BW368,BN386:BW421,BN439:BW474")
Set rng4 = Range("CJ15:CS50,CJ68:CS103,CJ121:CS156,CJ174:CS209,CJ227:CS262,CJ280:CS315,CJ333:CS368,CJ386:CS421,CJ439:CS474")
Set rng5 = Range("B15:B50, B68:B103, B121:B156, B174:B209, B227:B262, B280:B315, B333:B368, B386:B421, B439:B474")
Set rng6 = Range("X15:X50,X68:X103,X121:X156,X174:X209,X227:X262,X280:X315,X333:X368,X386:X421,X439:X474")
Set rng7 = Range("WS15:WS50")
Set rng8 = Range("WT15:WT50")
Set rng9 = Range("WW15:WW50")

If Not Intersect(c, rng1) Is Nothing Then
StopCalc
    Unprotect Password:=Sheet577.Range("H27").Value
    Range("XI23").Value = Range("XJ21").Value
    Range("WV15:WV50").Value = Range("CW15:CW50").Value
    Range("WV68:WV103").Value = Range("CW15:CW50").Value
    Range("WV121:WV156").Value = Range("CW15:CW50").Value
    Range("WV174:WV209").Value = Range("CW15:CW50").Value
    Range("WV227:WV262").Value = Range("CW15:CW50").Value
    Range("WV280:WV315").Value = Range("CW15:CW50").Value
    Range("WV333:WV368").Value = Range("CW15:CW50").Value
    Range("WV386:WV421").Value = Range("CW15:CW50").Value
    Range("WV439:WV474").Value = Range("CW15:CW50").Value
    Protect Password:=Sheet577.Range("H27").Value
ResetCalc
End If

If Not Intersect(c, rng1) Is Nothing Then
    Unprotect Password:=Sheet577.Range("H27").Value

    For Each c In rng5

       If Range(Cells(c.Row + 615).Value).Value = True And Range(Cells(c.Row + 616).Value).Value = False Or Range(Cells(c.Row + 615).Value).Value = True And Range(Cells(c.Row + 616).Value).Value = True And Range(Cells(c.Row + 619).Value).Value <> Empty Then 'change task number to black
            c.Interior.Color = RGB(0, 0, 0)
            c.Font.Color = RGB(255, 0, 0)
            c.Font.Bold = True

            If Range(Cells(c.Row + 484).Value).Value = "w" Then
                c.Interior.Color = RGB(255, 255, 255)
                c.Font.Color = RGB(0, 0, 0)
                c.Font.Bold = False
            End If
        End If

        If Range(Cells(c.Row + 616).Value).Value = True And Range(Cells(c.Row + 619).Value).Value <> Empty Then 'unsat given after sat
            Unprotect Password:=Sheet577.Range("H27").Value
            Range(Cells(c.Row + 53).Value).Value = True
            c.Interior.Color = RGB(255, 0, 0)
            c.Font.Color = RGB(0, 0, 0)
            c.Font.Bold = False
            Else
                Unprotect Password:=Sheet577.Range("H27").Value
                Range(Cells(c.Row + 53).Value).Value = False
                c.Interior.Color = RGB(255, 255, 255)
                c.Font.Color = RGB(0, 0, 0)
                c.Font.Bold = False
        End If

    Next c

    Protect Password:=Sheet577.Range("H27").Value
End If

If Not Intersect(c, rng2) Is Nothing Then
    Unprotect Password:=Sheet577.Range("H27").Value
    Range("XI23").Value = Range("XJ21").Value
    Range("XB15:XB50").Value = Range("CX15:CX50").Value
    Protect Password:=Sheet577.Range("H27").Value
End If

If Not Intersect(c, rng3) Is Nothing Then
    Unprotect Password:=Sheet577.Range("H27").Value
    Range("XI28").Value = Range("XJ26").Value
    Protect Password:=Sheet577.Range("H27").Value
End If

If Not Intersect(c, rng4) Is Nothing Then
    Unprotect Password:=Sheet577.Range("H27").Value
    Range("XI28").Value = Range("XJ26").Value
    Protect Password:=Sheet577.Range("H27").Value
End If


End Sub

Private Sub Worksheet_Deactivate()

    Unprotect Password:=Sheet577.Range("H27").Value
    Range("WV15:WV50,XB15:XB50").ClearContents
    Protect Password:=Sheet577.Range("H27").Value
   
End Sub
 

Attachments

  • Capture.JPG
    Capture.JPG
    179.6 KB · Views: 19
  • Capture2.JPG
    Capture2.JPG
    154.8 KB · Views: 17
  • Capture3.JPG
    Capture3.JPG
    163.3 KB · Views: 19
  • Capture4.JPG
    Capture4.JPG
    31.3 KB · Views: 19
  • Capture5.JPG
    Capture5.JPG
    143.5 KB · Views: 19

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
@dsheard2015

I think you will find it's the way you are trying to reference the ranges.

Eg
VBA Code: 
If Range(Cells(c.Row + 484).Value).Value = "w" Then
. would error in the same way if it could progress beyond your first error which is a long succession of similarly expressed ranges.
As for suggesting a correction, I'm afraid I'm struggling as I cannot properly understand your bigger picture.
Certainly, the Cells reference appears to only have the Row parameter! Is the Column totally missing or should your + sign be the comma delimiter, as in. Cells(c.Row , 484)?
Is the cells().value returning text as the address within your Range() ?????

Food for thought hopefully.
 
Upvote 0
Snakehips said:
@dsheard2015

I think you will find it's the way you are trying to reference the ranges.

Eg
VBA Code: 
If Range(Cells(c.Row + 484).Value).Value = "w" Then
. would error in the same way if it could progress beyond your first error which is a long succession of similarly expressed ranges.
As for suggesting a correction, I'm afraid I'm struggling as I cannot properly understand your bigger picture.
Certainly, the Cells reference appears to only have the Row parameter! Is the Column totally missing or should your + sign be the comma delimiter, as in. Cells(c.Row , 484)?
Is the cells().value returning text as the address within your Range() ?????

Food for thought hopefully.
Click to expand...
Snakehips said:
@dsheard2015

I think you will find it's the way you are trying to reference the ranges.

Eg
VBA Code: 
If Range(Cells(c.Row + 484).Value).Value = "w" Then
. would error in the same way if it could progress beyond your first error which is a long succession of similarly expressed ranges.
As for suggesting a correction, I'm afraid I'm struggling as I cannot properly understand your bigger picture.
Certainly, the Cells reference appears to only have the Row parameter! Is the Column totally missing or should your + sign be the comma delimiter, as in. Cells(c.Row , 484)?
Is the cells().value returning text as the address within your Range() ?????

Food for thought hopefully.
Click to expand...


Hello Eg, Thanks for your response! You were absolutely correct about my column references, not sure how that happened. I made the corrections and am still getting that same error on the same line. I think it has something to do with how the variables are declared but honestly, I'm not familiar enough to have figured that out yet.

This worksheet is part of a very large and involved workbook. I will briefly explain what I am trying to accomplish to hopefully make things easier to understand. I have also included some images of the worksheet to better explain the layout.

Here is the updated worksheet change event code....

VBA Code: 
Private Sub Worksheet_Change(ByVal c As Range)

Dim rng1 As Range 'page 1 grades
Dim rng2 As Range 'page 2 grades
Dim rng3 As Range 'previous page 1 grades
Dim rng4 As Range 'previous page 2 grades
Dim rng5 As Range 'page 1 task numbers
Dim rng6 As Range 'page 2 task numbers
Dim rng7 As Range 'WS column
Dim rng8 As Range 'WT column
Dim rng9 As Range 'WW column

Set rng1 = Range("M15:V50,M68:V103,M121:V156,M174:V209,M227:V262,M280:V315,M333:V368,M386:V421,M439:V474")
Set rng2 = Range("AI15:AR50,AI68:AR103,AI121:AR156,AI174:AR209,AI227:AR262,AI280:AR315,AI333:AR368,AI386:AR421,AI439:AR474")
Set rng3 = Range("BN15:BW50,BN68:BW103,BN121:BW156,BN174:BW209,BN227:BW262,BN280:BW315,BN333:BW368,BN386:BW421,BN439:BW474")
Set rng4 = Range("CJ15:CS50,CJ68:CS103,CJ121:CS156,CJ174:CS209,CJ227:CS262,CJ280:CS315,CJ333:CS368,CJ386:CS421,CJ439:CS474")
Set rng5 = Range("B15:B50, B68:B103, B121:B156, B174:B209, B227:B262, B280:B315, B333:B368, B386:B421, B439:B474")
Set rng6 = Range("X15:X50,X68:X103,X121:X156,X174:X209,X227:X262,X280:X315,X333:X368,X386:X421,X439:X474")
Set rng7 = Range("WS15:WS50")
Set rng8 = Range("WT15:WT50")
Set rng9 = Range("WW15:WW50")

If Not Intersect(c, rng1) Is Nothing Then
StopCalc
    Unprotect Password:=Sheet577.Range("H27").Value
    Range("XI23").Value = Range("XJ21").Value
    Range("WV15:WV50").Value = Range("CW15:CW50").Value
    Range("WV68:WV103").Value = Range("CW15:CW50").Value
    Range("WV121:WV156").Value = Range("CW15:CW50").Value
    Range("WV174:WV209").Value = Range("CW15:CW50").Value
    Range("WV227:WV262").Value = Range("CW15:CW50").Value
    Range("WV280:WV315").Value = Range("CW15:CW50").Value
    Range("WV333:WV368").Value = Range("CW15:CW50").Value
    Range("WV386:WV421").Value = Range("CW15:CW50").Value
    Range("WV439:WV474").Value = Range("CW15:CW50").Value
    Protect Password:=Sheet577.Range("H27").Value
ResetCalc
End If

If Not Intersect(c, rng1) Is Nothing Then
    Unprotect Password:=Sheet577.Range("H27").Value

    For Each c In rng5

        If Range(Cells(c.Row, 617).Value).Value = True And Range(Cells(c.Row, 618).Value).Value = False Or Range(Cells(c.Row, 617).Value).Value = True And Range(Cells(c.Row, 618).Value).Value = True And Range(Cells(c.Row, 621).Value).Value <> Empty Then    'change task number to black
            c.Interior.Color = RGB(0, 0, 0)
            c.Font.Color = RGB(255, 0, 0)
            c.Font.Bold = True

            If Range(Cells(c.Row, 484).Value).Value = "w" Then
                c.Interior.Color = RGB(255, 255, 255)
                c.Font.Color = RGB(0, 0, 0)
                c.Font.Bold = False
            End If
        End If

        If Range(Cells(c.Row, 618).Value).Value = True And Range(Cells(c.Row, 621).Value).Value <> Empty Then 'unsat given after sat
            Unprotect Password:=Sheet577.Range("H27").Value
            Range(Cells(c.Row, 55).Value).Value = True
            c.Interior.Color = RGB(255, 0, 0)
            c.Font.Color = RGB(0, 0, 0)
            c.Font.Bold = False
            Else
                Unprotect Password:=Sheet577.Range("H27").Value
                Range(Cells(c.Row, 55).Value).Value = False
                c.Interior.Color = RGB(255, 255, 255)
                c.Font.Color = RGB(0, 0, 0)
                c.Font.Bold = False
        End If

    Next c

    Protect Password:=Sheet577.Range("H27").Value
End If

'If Not Intersect(c, rng2) Is Nothing Then
'    Unprotect Password:=Sheet577.Range("H27").Value
'    Range("XI23").Value = Range("XJ21").Value
'    Range("XB15:XB50").Value = Range("CX15:CX50").Value
'    Protect Password:=Sheet577.Range("H27").Value
'End If
'
'If Not Intersect(c, rng3) Is Nothing Then
'    Unprotect Password:=Sheet577.Range("H27").Value
'    Range("XI28").Value = Range("XJ26").Value
'    Protect Password:=Sheet577.Range("H27").Value
'End If
'
'If Not Intersect(c, rng4) Is Nothing Then
'    Unprotect Password:=Sheet577.Range("H27").Value
'    Range("XI28").Value = Range("XJ26").Value
'    Protect Password:=Sheet577.Range("H27").Value
'End If


End Sub


There are a total of 9 pages on this worksheet, each page is the same, but each page is basically a continuation of the page above it. I will explain only the top page but all the ranges in each page all work together. B15:B50 is populated with a task # in each cell, all 9 pages have the same tasks. M15:V50 is where the grade data is entered for each task, M15:V15 are the grade cells for the task in B15, and so on down the page. The intent of the vba code is to change the formatting of the task cell, B15....based on the grades entered in M15:V15.... . When cell B15 changes on page 1 then it also changes on all the other pages as well.

Hopefully that helps to explain what is going on with that part of the code. If you are able to assist with correcting what I've got then I would be very appreciative.

Thanks again,

Dave
 
Upvote 0
As an example, 'Eg' :), what typical return might you expect to be getting from...... Cells(c.Row, 617).Value
 
Upvote 0
@dsheard2015 Dave, Maybe ignore that last post. I see from your post 1 images that the values are likely True / False.

So that return would invalidate the Range expression. Eg.... Range(FALSE).Value
So, try removing your superfluous Range expressions. Eg.....
VBA Code: 
If Cells(c.Row, 617).Value = True And.....
 
Upvote 0
Snakehips said:
As an example, 'Eg' :), what typical return might you expect to be getting from...... Cells(c.Row, 617).Value
Click to expand...

oops, sorry. I missed that question the first time. on that line of code, the 617, 618 and 55 references will return either true or false and the 621 reference will return a whole number (0-35), and 484 will return either a "w" or "y".
 
Upvote 0
Snakehips said:
@dsheard2015 Dave, Maybe ignore that last post. I see from your post 1 images that the values are likely True / False.

So that return would invalidate the Range expression. Eg.... Range(FALSE).Value
So, try removing your superfluous Range expressions. Eg.....
VBA Code: 
If Cells(c.Row, 617).Value = True And.....
Click to expand...

ah, ok. Well that makes total sense. Thanks for pointing that out. I am still learning vba so I do appreciate your comments. I will try and come up with some changes.

Thanks Tony!
 
Upvote 0
Snakehips, I made those changes and the errors do not appear anymore. The code appears to be working partially now, the only thing I still need to fix now is the cell colors in column B. I will take a look at it tomorrow morning. Thanks again for all your help!
 
Upvote 0
Dave, just had a quick look and noticed that you have lines within that event code that could trigger itself again and again.

See below how you should in some appropriate way, disable events at some point but then ensure that events are re-enabled before the sub is exited. Below is a simple inclusion of that which may or may not suffice. Not the whole story but may be another obsticle removed?

VBA Code: 
Private Sub Worksheet_Change(ByVal c As Range)

Dim rng1 As Range 'page 1 grades
Dim rng2 As Range 'page 2 grades
Dim rng3 As Range 'previous page 1 grades
Dim rng4 As Range 'previous page 2 grades
Dim rng5 As Range 'page 1 task numbers
Dim rng6 As Range 'page 2 task numbers
Dim rng7 As Range 'WS column
Dim rng8 As Range 'WT column
Dim rng9 As Range 'WW column

Set rng1 = Range("M15:V50,M68:V103,M121:V156,M174:V209,M227:V262,M280:V315,M333:V368,M386:V421,M439:V474")
Set rng2 = Range("AI15:AR50,AI68:AR103,AI121:AR156,AI174:AR209,AI227:AR262,AI280:AR315,AI333:AR368,AI386:AR421,AI439:AR474")
Set rng3 = Range("BN15:BW50,BN68:BW103,BN121:BW156,BN174:BW209,BN227:BW262,BN280:BW315,BN333:BW368,BN386:BW421,BN439:BW474")
Set rng4 = Range("CJ15:CS50,CJ68:CS103,CJ121:CS156,CJ174:CS209,CJ227:CS262,CJ280:CS315,CJ333:CS368,CJ386:CS421,CJ439:CS474")
Set rng5 = Range("B15:B50, B68:B103, B121:B156, B174:B209, B227:B262, B280:B315, B333:B368, B386:B421, B439:B474")
Set rng6 = Range("X15:X50,X68:X103,X121:X156,X174:X209,X227:X262,X280:X315,X333:X368,X386:X421,X439:X474")
Set rng7 = Range("WS15:WS50")
Set rng8 = Range("WT15:WT50")
Set rng9 = Range("WW15:WW50")

'******************************
'Disable events or cell value changes made by this this code will trigger itself again part way through
Application.EnableEvents = False
'Direct errors to clean exit
On Error Goto Done:
'*****************************
If Not Intersect(c, rng1) Is Nothing Then

StopCalc
    Unprotect Password:=Sheet577.Range("H27").Value
    Range("XI23").Value = Range("XJ21").Value
    Range("WV15:WV50").Value = Range("CW15:CW50").Value
    Range("WV68:WV103").Value = Range("CW15:CW50").Value
    Range("WV121:WV156").Value = Range("CW15:CW50").Value
   Range("WV174:WV209").Value = Range("CW15:CW50").Value
    Range("WV227:WV262").Value = Range("CW15:CW50").Value
    Range("WV280:WV315").Value = Range("CW15:CW50").Value
   Range("WV333:WV368").Value = Range("CW15:CW50").Value
    Range("WV386:WV421").Value = Range("CW15:CW50").Value
    Range("WV439:WV474").Value = Range("CW15:CW50").Value
    Protect Password:=Sheet577.Range("H27").Value
ResetCalc
End If

If Not Intersect(c, rng1) Is Nothing Then
    Unprotect Password:=Sheet577.Range("H27").Value

    For Each c In rng5

       If Cells(c.Row, 617).Value = True And Cells(c.Row, 618).Value = False Or Cells(c.Row, 617).Value = True And Cells(c.Row, 618).Value = True And Cells(c.Row, 621).Value <> Empty Then    'change task number to black
            c.Interior.Color = RGB(0, 0, 0)
            c.Font.Color = RGB(255, 0, 0)
            c.Font.Bold = True

           If Range(Cells(c.Row, 484).Value).Value = "w" Then
                c.Interior.Color = RGB(255, 255, 255)
                c.Font.Color = RGB(0, 0, 0)
                c.Font.Bold = False
            End If
        End If

        If Cells(c.Row, 618).Value = True And Cells(c.Row, 621).Value <> Empty Then 'unsat given after sat
            Unprotect Password:=Sheet577.Range("H27").Value
            Cells(c.Row, 55).Value = True   ''' Would trigger change event!!!
            c.Interior.Color = RGB(255, 0, 0)
            c.Font.Color = RGB(0, 0, 0)
            c.Font.Bold = False
           Else
               Unprotect Password:=Sheet577.Range("H27").Value
                Cells(c.Row, 55).Value = False  ''' Would trigger change event!!!
                c.Interior.Color = RGB(255, 255, 255)
                c.Font.Color = RGB(0, 0, 0)
                c.Font.Bold = False
        End If

    Next c

    Protect Password:=Sheet577.Range("H27").Value
End If

'If Not Intersect(c, rng2) Is Nothing Then
'    Unprotect Password:=Sheet577.Range("H27").Value
'    Range("XI23").Value = Range("XJ21").Value   ''' Would trigger change event!!!
'    Range("XB15:XB50").Value = Range("CX15:CX50").Value    ''' Would trigger change event!!!
'    Protect Password:=Sheet577.Range("H27").Value
'End If
'
'If Not Intersect(c, rng3) Is Nothing Then
'    Unprotect Password:=Sheet577.Range("H27").Value
'    Range("XI28").Value = Range("XJ26").Value       ''' Would trigger change event!!!
'    Protect Password:=Sheet577.Range("H27").Value
'End If
'
'If Not Intersect(c, rng4) Is Nothing Then
'    Unprotect Password:=Sheet577.Range("H27").Value
'    Range("XI28").Value = Range("XJ26").Value     ''' Would trigger change event!!!'
'    Protect Password:=Sheet577.Range("H27").Value
'End If

'****************************************
'redirect any errors or early exit option to label Done in order to have clean exit ??
Done:
'******Reset error handling to default and re enable Events before exiting this code*****
On Error Goto 0
Application.EnableEvents = True

End Sub

See if that helps you move forward.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Run-time error 1004 method range of object worksheet railed
Replies
2
Views
108
Joe4
Joe4
M
  • Question Question
Run time error 1004: Application defined or object defined error
Replies
1
Views
588
Micron
M
C
  • Question Question
Cut a row from "sheet 1" and paste onto "sheet2", run-time error '1004' "Paste method of Worksheet class failed"
2
Replies
10
Views
1K
rpaulson
rpaulson
2
  • Question Question
Run=time error '1004': Method 'Range of Object'_Global' failed
Replies
4
Views
665
2077delta
2
J
  • Question Question
Run-time error '1004': The extract range has a missing or invalid field name.
2
Replies
15
Views
1K
josros60
J

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top