Hello

Would someone please help me in tweaking this macro? I asked for help with this on another forum [ here ] but for some reason the person helping me said I was taking advantage of their kindness and I most certainly was not.

I simply asked them to tell me where I need to alter the macro shown below to suit my needs since he never used the information I provided regarding sheets/column/cell references.

I'm a novice when it comes to this and sometimes I'm not even sure of the correct terminology to use so I hope someone will please help me.


Original Code = My comments in Bold Red

Notes to the code

In this code we assume

1- Completion Date is in column B in the range B19:B32 – Pulling from sheets A-Z, Range AZ19:AZ50

2- Due Date is in column C on similar range C19:C32 – Pulls from hidden sheet “.” Range J14:J45

3- Item/compliance is in column A on similar range A19:A32 – Pulls from hidden sheet “.” Range C14:C45 OR Pulls from sheets A-Z, range AV19:AV50

4 - 26 of the total sheets on your workbook are named with the alphabet letters from A to Z - Do I have to update this in the code somewhere?

Replace the ranges in the code with the ones in your scenario -
Replace the messages according to your needs

''''***********************************************************

Private Sub Workbook_Open()

Dim CompletionRng As Range

Dim rng As Range

Dim DueDate As Range

Dim compliance As Range

Dim workingdays As Double

Dim Sh As Worksheet

Dim i As Integer



For i = 65 To 90 '' Loop thru sheets A to Z

Set Sh = Sheets(Chr(i))



With Sh

On Error GoTo getOut

Set CompletionRng = .Range("B19:B32").SpecialCells(xlCellTypeBlanks)

For Each rng In CompletionRng

Set DueDate = .Range("C" & rng.Row)

Set compliance = .Range("A" & rng.Row)

workingdays = Application.WorksheetFunction.NetworkDays(Date, DueDate)

Select Case workingdays

Case Is = 3, 10

MsgBox Prompt:="Alert:" & vbNewLine & "REF. COMPLIANCE: " & compliance & vbNewLine & "Due Date expires in " & workingdays & " working days", Buttons:=vbExclamation, Title:="DUE DATE REMINDER"

Case Is = 1

MsgBox Prompt:="THAT'S IT, you are DONE", Buttons:=vbCritical, Title:="DUE DATE REMINDER"

End Select

Next rng



End With

Next i

getOut:



End Sub

''''''''''*******************************************************************

Please do not suggest conditional formatting. I do not need nor want to do that. This code will be used in 3 different workbooks that each contain approximately 60 sheets.

Please know that I appreciate any assistance I can get with this.