Hello,
I am using the below code to highlight cells and add comments in the main sheet "Audit Plan". The macro is initiated by clicking a button in the "Control" Sheet and from there the code will sun the StatusUpdate code in the 'Audit Plan' sheet and then it will run the WeeklyTrackerNewAudits code in the Check_WeeklyTracker sheet.
I am getting a Run-time error '13': Type mismatch in the section below and I'm not sure what I did wrong. Separated each code runs perfectly, but combined in does not run as smooth.
This is where the error occurs.
What am I missing when I combine into one module?
Thank you,
I am using the below code to highlight cells and add comments in the main sheet "Audit Plan". The macro is initiated by clicking a button in the "Control" Sheet and from there the code will sun the StatusUpdate code in the 'Audit Plan' sheet and then it will run the WeeklyTrackerNewAudits code in the Check_WeeklyTracker sheet.
Excel Formula:
Option Explicit
Sub ReconCheck()
Call StatusUpdate
Call WeeklyTrackerNewAudits
MsgBox ("Updates highlighted and noted")
End Sub
Sub StatusUpdate()
Dim i As Long, lrow As Long, iCol As Long, dt As String
Dim sh As Worksheet
Set sh = Sheets("Audit_Plan")
iCol = sh.Range("A1").CurrentRegion.Columns.Count
lrow = sh.Range("J" & Rows.Count).End(xlUp).Row
dt = Format(sh.Range("K3"), "mm/dd/yyyy") & " - "
sh.Activate
For i = 7 To lrow
If Cells(i, "G").Value <> Cells(i, "CR").Value Then
Cells(i, 1).Resize(, iCol).Interior.ColorIndex = 6
If Cells(i, "G").Value = "In Progress" And Cells(i, "CR").Value = "Not Started" Then
Cells(i, 83).Value = "Other"
Cells(i, 84).Value = dt & "Status Updated from 'Not Started' to 'In Progress' - Need to update in SharePoint"
End If
End If
If Cells(i, "G").Value = "Cancelled" Then
Cells(i, 83).Value = "Remove from Audit Plan"
Cells(i, 84).Value = dt & "Cancelled per Weekly Tracker"
End If
If Cells(i, "G").Value = "Completed" And Cells(i, "CR").Value <> "Completed" Then
Cells(i, 83).Value = "Published - Report Not Received"
Cells(i, 84).Value = dt & "Published per Weekly Tracker"
End If
Next i
End Sub
Sub WeeklyTrackerNewAudits()
Dim j As Long, lrow As Long, dt As String
Dim sh3 As Worksheet, sh2 As Worksheet
Set sh3 = Sheets("Check_WeeklyTracker")
Set sh2 = Sheets("Control")
lrow = sh3.Range("A" & Rows.Count).End(xlUp).Row
dt = sh2.Range("B10")
sh.Activate
For j = 6 To lrow
If Cells(j, "K").Value > dt And IsError(Cells(i, "V").Value) Then
Cells(j, 1).Interior.ColorIndex = 3
End If
Next i
End Sub
I am getting a Run-time error '13': Type mismatch in the section below and I'm not sure what I did wrong. Separated each code runs perfectly, but combined in does not run as smooth.
This is where the error occurs.
VBA Code:
For i = 7 To lrow
If Cells(i, "G").Value <> Cells(i, "CR").Value Then
What am I missing when I combine into one module?
Thank you,