Macro runs into "object required" error, please debug.

[AlexCHI]

Greetings All,

I am unfamiliar with VBA so reaching out to anyone who can help me debug why the macro named "Sheet2.ShowMembership" runs into "Object required" error message when fired?

The macro in question basically turns column A & B (last name & first name) into bolded text with yellow fill when a person reaches a consecutive 6-month attendance (or as written in the VBA, 26 consecutive attendance indicated by either "AM", "PM", or "AM & PM" from column T:NT.

If I changed the "Sub ShowMembership()" to "Private Sub Worksheet_Change(ByVal Target As Range)" then it works beautifully by itself but I have to sacrifice the other VBA with the same sub which is date and day input in B3 and B5.

Ideally is there a way to modify the last two VBAs in the attached file so they can coexist as Worksheet_Change? If not, please teach me how to correct the ShowMembership macro.

Please access the file here:
https://drive.google.com/open?id=1KdGqbExqYBIXcLiDkVhyGrPkOB-cFC9V

Thank you in advance for your time and help!

 

[dmt32]

Hi,
I think you have kind of answered your own question.
Worksheet_Change event has a range argument "Target" which you have included in your procedure but not passed this to it when called.

Try these changes

ShowMembership code add the argument

Sub ShowMembership(ByVal Target As Range)

From where ever required in the worksheet_change event

ShowMembership Target

This should now pass the range to your code.

I also noticed a typo with your declaration

You have declared
Dim xAdress As String

but in the code you use

xAddress = "K:R"

you can avoid this by placing Option Explicit at very TOP of the module / code page where compiler will report missing variables.

Hope Helpful

Dave