"Ambiguous name detected: FormatCell"

[copleyr]

Hello,

I need my macros to search for the word "Cancel" in columns "T". Once found, I need the macros to make that entire row a certain pattern ( .Pattern = xlGray16)

There will be other wording in these cells that contain "Cancel" or "Cancelled".

A compile error keeps occurring. It says "Ambiguous name detected: FormatCell"..

I'm confused with it..... any suggestions?

Please Login or Register  to view this content.

Thank you in advance for your help.

[shg]

You have two copies of the sub in the module. Delete one.

Or replace all the code with this:

Please Login or Register  to view this content.

[copleyr]

Thanks for the same reply. I received the exact same error message.

[shg]

Search for FormatCell project-wide. There are two (or more) copies. Delete all but one.

[copleyr]

those were the only two in the project i just Control + F 'd

[shg]

Post the workbook. You can delete all the data.

[copleyr]

whats your email ill send you all of my code in the project

[copleyr]

These are all of my subs:

Could this have something to do with it?

Please Login or Register  to view this content.

[shg]

Delete all the data and post the workbook.

[Leith Ross]

Hello copleyr,

Please read the forum rules, especially about cross posting.
Cross posted here

Forum Rules

[copleyr]

Please see attached

[Leith Ross]

Hello copleyr,

Most forums don't mid if you cross post provided you add a link to the other sites. This helps everyone know were you are currently with answers and code. It helps prevent duplication of effort and wasting of time on the part of the posters.

To Attach a File:
1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Leith Ross]

Hello copleyr,

You have the following two Subs named the same in Module7. An easy way to avoid this in the future is to add the statement Option Explicit as the first line of the module. This will force you to declare your variables.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[copleyr]

Thanks for the quick reply!

I put "option explicit" before the sub on each sub that I listed. I still gave me the same error in the same place?

any ideas?

[copleyr]

Please view attached screenshot.

[Leith Ross]

Hello copleyr,

The rule is each sub inside the same module must be unique, i.e. the names must be different. Change the names of the subs. Add a number to each one if you want, so you have FormatCell1 and FormatCell2.

[copleyr]

Thanks Leith I surely appreciate your help. I will re word the names now.


Sorry Shg, kind of new to macros.

[copleyr]

Hey all,

I ended up being able to run the macros, but nothing worked. No row was set to this pattern.


Any suggestions on how i can fix the code to get it to work?

Thank you

Please Login or Register  to view this content.

[copleyr]

Let me clarify,

It will only work if it finds just the word "cancel" in the field and nothing else.

I need it to find the word "cancel" or "cancelled" in what could very well be a sentence in the cell. After realizing that the words "cancel" or "cancelled" was in this sentence, I need it to highlight the entirerow.

Is there a way I can do this?

[Leith Ross]

Hello copleyr,

This macro will replace the two in your previous post. It finds any form of "cancel" either as a word or in a sentence and grays the entire row when a match is made.

Please Login or Register  to view this content.

[copleyr]

Thanks Leigh everything worked. I appreciate everything!