Runtime Error only on some sheets - Macro reference "Active Sheet"

C

ckdragon

New Member
Joined
Apr 3, 2022
Messages
37
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi Team,

I am super stumped with this one and hoping someone might be able to help!

I have 13 sheets in my workbook. All are the same layout/output, except that they have different reference tables which gets updated with new data from 2 "RAW" tables in the spreadsheet. The spreadsheet is used on a daily basis and there is a template file which is copied for each new day before data is run etc.

On each sheet I have a button which is supposed to 'format' the sheet based on the data imported and subsequent results of various formulas, Lookups or other referenced cells.
most of the formatting is just highlighting the cell a certain colour so that it is easier for the user to see what is important/what can be disregarded based on

The button is embedded with 6 Macros and then a message box saying "Done".

All of the Macros reference the "Active Sheet" in various forms, as the sheets are only looked at in isolation and are only worked on 1 at a time.

The problem that I am having is that sometimes, the button and therefore formatting works without any problems at all but other times I get a Runtime 13 error and it stops working all together for that sheet.

The problem seems to stem from Macro3 or sometimes Macro4 onwards and when I debug it, it essentially shows that the first line of the Macro is the problem - regardless of what that is (I have tried changing it to be different variable to see if that is the problem but that hasn't fixed it)

I understand that in some cases the Macros are all referencing the same cell, which is why when I was building the button I stacked them so each Macro calls and completes and then should override any formatting in that cell that may have applied so the final format is what it should be.

The Various Code is below - if anyone can see any error and can fix, that would be amazing - or if anyone has any other ideas that would be Fab.

Thank you

MACRO6
VBA Code: 
Sub Formatting_FINAL()

Dim MyRange As Range
Set MyRange = ActiveSheet.Range("A1:V38")

For Each cell In MyRange

If cell.Value = "Early" Then
cell.Interior.Color = RGB(255, 0, 0)
End If

If cell.Value = "Late" Then
cell.Interior.Color = RGB(255, 0, 0)
End If

If cell.Value = "ERROR" Then
cell.Interior.Color = RGB(255, 0, 0)
End If

If cell.Value = "YES - MANUAL PROCESS" Then
cell.Interior.Color = RGB(255, 0, 0)
End If

If cell.Value = "In Booking Time" Then
cell.Interior.Color = RGB(146, 208, 80)
End If

If cell.Value = "TRUE" Then
cell.Interior.Color = RGB(146, 208, 80)
End If

If cell.Value = "NO - AUTO CALCULATION" Then
cell.Interior.Color = RGB(146, 208, 80)
End If

If cell.Value = "NO" Then
cell.Interior.Color = RGB(146, 208, 80)
End If

If cell.Value = "Acceptably Early" Then
cell.Interior.Color = RGB(255, 217, 102)
End If

If cell.Value = "Acceptably Late" Then
cell.Interior.Color = RGB(255, 217, 102)
End If

If cell.Value = "YES" Then
cell.Interior.Color = RGB(255, 217, 102)
End If

If cell.Value = "Review TMR Data to Match Booking" Then
cell.Interior.Color = RGB(189, 215, 238)
End If

If cell.Value = "Multi-day Booking" Then
cell.Interior.Color = RGB(183, 138, 192)
End If

Next cell

End Sub


MACRO4
VBA Code: 
Sub Formatting_John_Billing_bookings_FINAL()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
Dim DesSheet As Worksheet
Set DesSheet = ActiveSheet

With DesSheet
    'Define First and Last Rows
    FirstRow = 18
    LastRow = 31
    'Loop Through Rows (Bottom to Top)
    For Row = LastRow + 1 To FirstRow Step -1
        If .Range("B" & Row).Value = "** John Doe **" Then
        .Range("B" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("J" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("K" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("L" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("M" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("N" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("O" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("P" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("Q" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("R" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("S" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("T" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("U" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("V" & Row).Interior.Color = RGB(242, 242, 242)
         End If
        
         If .Range("G" & Row).Value = "** John Doe **" Then
        .Range("B" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("J" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("K" & Row).Interior.Color = RGB(242, 242, 242)
         End If
      
    Next Row
    
End With

    ActiveSheet.Select
End Sub

MACRO5
VBA Code: 
Sub Formatting_Invoice_Summary_FINAL()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
Dim DesSheet As Worksheet
Set DesSheet = ActiveSheet

With DesSheet
    'Define First and Last Rows
    FirstRow = 7
    LastRow = 16
    'Loop Through Rows (Bottom to Top)
    For Row = LastRow + 1 To FirstRow Step -1
        If .Range("C" & Row).Value = "** John Doe **" Then
        .Range("B" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(-1).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
      End If
      
        If .Range("E" & Row).Value = "** John Doe **" Then
        .Range("D" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Offset(-1).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("D" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("E" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
      End If
      
            If .Range("G" & Row).Value = "** John Doe **" Then
        .Range("F" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Offset(-1).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("F" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("G" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
      End If
      
      
        If .Range("I" & Row).Value = "** John Doe **" Then
        .Range("H" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(-1).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
      End If
      
        If .Range("I" & Row).Value = "** John Doe **" Then
        .Range("H" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(-1).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("H" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("I" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
      End If
      
      
    Next Row
    
End With

    ActiveSheet.Select
End Sub


MACRO3
VBA Code: 
Sub AutofitRowFINAL()


ActiveSheet.Cells.EntireRow.AutoFit


End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Do you have any error values in your sheets?
 
Upvote 0
RoryA said:
Do you have any error values in your sheets?
Click to expand...
Maybe sometimes there will be a random error val come up (I am working on fixing those so they either show blank or something else)

Would an error in the sheet/range cause a Runtime error on a macro?
 
Upvote 0
Yes. If a cell contains an error and you try and compare its value to anything other than an error value, you will get a type mismatch.
 
Upvote 0
RoryA said:
Yes. If a cell contains an error and you try and compare its value to anything other than an error value, you will get a type mismatch.
Click to expand...

Oh okay!! That helps heaps. Thank you.
If it keeps happening after I fix all them then I will go from there

Thank you so much
 
Upvote 0
Also, since you are colouring blocks of cells, you can replace something like this:

VBA Code: 
        .Range("B" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(-1).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("B" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(1).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(2).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(3).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(4).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(5).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(6).Interior.Color = RGB(242, 242, 242)
        .Range("C" & Row).Offset(7).Interior.Color = RGB(242, 242, 242)

with this:

VBA Code: 
With .Range("B" & Row)
    .Resize(8, 2).Interior.Color = RGB(242, 242, 242)
    .Offset(-1).Interior.Color = RGB(242, 242, 242)
End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Conditional formatting Coloring due dates
Replies
4
Views
127
JAZ91
J
J
  • Question Question
Multiple conditional formatting
Replies
6
Views
173
StephenCrump
S
F
  • Solved
VBA set conditional formatting application-defined or object-defined error
Replies
3
Views
402
Cubist
Cubist
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
M
  • Question Question
How to Make this Macro work Fast?
Replies
2
Views
483
Georgiboy
Georgiboy

Forum statistics

Threads
1,223,883
Messages
6,175,167
Members
452,615
Latest member
bogeys2birdies

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