Conditional Formatting based on Defined Names.

[eyerukhi]

Hi all,
I would really appreciate if someone could help me with the following problem:
I wrote this code, but for some reason it doesn't work. I am completely new to VBA and have no idea what went wrong:

Please Login or Register  to view this content.

When I run this macro I can see that it does something. For example, when I click on columns C and H and go to conditional formatting, I can see that the formulas are there. Aslo, all the names are defined appropriately. The only problem is that names that are supposed to refer to the ranges in a different work book, refer to nowhere. Basically their "Refer to" window contains path like that:

[FileName]Sheet2!$C:$H

It is my understanding that this string should contain a real path to the second workbook, not a FileName variable, but it doesn't. What am I doing wrong?

Thank you in advance,
Eugenia.

[Paul]

Hello Eugenia, and welcome to the forum.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[shg]

Also, please amend your thread title to describe your problem.

Thanks.

[eyerukhi]

I hope the new title is more appropriate.

Regards,
Eugenia.

[Paul]

On your first post in the thread, click the Edit button. Then click the 'Go Advanced' button. From there you can edit the Title, and you can also add code tags around your original post's code rather than creating a new post in the thread.

[shg]

Thank you for the code tags

I can't find an option that would allow me to amend the title of the thread. Could you please give me additional instructions on how to do that?

Yes; read the Forum Rules

[eyerukhi]

Hi all,

I actually changed the code slightly, added these two lines:

Please Login or Register  to view this content.

But it did not fix my problem. There is something wrong with the defined names that refer to the ranges in a different workbook. I would really appreciate any suggestions.

Here is the complete updated code:

Please Login or Register  to view this content.

Thanks,
Eugenia.

[shg]

For starters, change RefersToR1C1 to RefersTo. You're using A1 references, not R1C1.

[eyerukhi]

No, I am using R1C1 references when I define names. For example, C3 refers to the whole column C.

[shg]

Oops, sorry.

Is the correct workbook active when the code runs?

Instead of

Please Login or Register  to view this content.

how about

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or use the workbook when defining the names instead of ActiveWorkbook.

The code ran fine for me.

[eyerukhi]

Yes,
I have the correct book active when the code is executed. You see, I want the original workbook to be always active while the macro runs because that is where I want all the formatting to be done. And everything actually works fine, except for the defined names that refer to the second workbook. After I run the macro and go to see where the ForeignRange refers to, I see this in the “refers to” window:

[FileName]Sheet2!$C:$H

However, I am pretty sure that at this stage it should look something like this:
[C:\Documents and Settings\blah-blah.xls] Sheet2!$C:$H

Basically, for some reason the FileName variable doesn’t become substituted by the actual path to the file when the macro runs.

That is why I think the problem has something to do with the way I use the FileName variable when I define names. May be I should dim FileName as something else or may be I should use different syntax when I use the variable in this line:

Please Login or Register  to view this content.

And I forgot to mention that I cannot use the actual name of the workbook when I define range names because it is unknown and depends on the file that a user opens.



Thanks,
Eugenia.

[shg]

It is a string -- I assumed you entered the actual filename.

Please Login or Register  to view this content.

[eyerukhi]

I tried this:

Please Login or Register  to view this content.

But it doesn't work. What am I missing?

Thanks.

[shg]

Mispositioned apostrophe ...

Please Login or Register  to view this content.

[eyerukhi]

Unfortunately it doesn't work either. As soon as I type the " after the &FileName&, Excel gives me a compile error message that says:"Expected: End of Statement." And the whole line turns red.

Please Login or Register  to view this content.

Should I put spaces between & and FileName?

[shg]

Sorry for all the flailing:

Please Login or Register  to view this content.

[eyerukhi]

Doesn't work. As soon as I type double quotes around &sFile& Excel marks the line red and gives me a compile error. Can it be that this problem is related to the fact that I have Excel 2000?

Please Login or Register  to view this content.

Thanks.

[shg]

Put spaces before and after the ampersands as in my example.

[eyerukhi]

Thank you so much

Now everything works perfect, except for this part:

Please Login or Register  to view this content.

The names are defined appropriately and the C column is formatted corectly. The only problem is with column H (code above). That is strange because the formular worked when I used it outside of the macro.

Sorry for consuming so much of your time.
I really appreciate your help!

P.S.: I am leaving right now, but I will be back tomorrow morning.
Thank you once again!

Regards,
Eugenia.

[shg]

LocalRange and ForeignRange need refer to ranges on the sheet where the validation is, but they cannot be names with sheet scope on another sheet (they can be names with workbook scope).

This worked fine for me:

Please Login or Register  to view this content.

Why, though, would you apply conditional formatting to the entire column?

[eyerukhi]

Hi,
I am back. All these days I tried on my own to figure out what is wrong with the macro and miserably failed. The second column is still not formatted the way I need it to be.

Thank you for the reply, I didn’t see it until now. I tried to substitute the problematic spot with your code, but unfortunately it didn’t work.

I am attaching the files that I am using to test the macro. They represent a simplified version of the real files I am working with, which I can’t post here because they contain real account numbers. In the example files, column C contains account numbers and column H contains the account balance ($). I open ExampleFile1 first and then run the macro, which prompts me to open ExampleFile2.

The code of the macro is:

Please Login or Register  to view this content.

It didn’t change since my last posting because all the adjustments I tried to make didn’t work.

Why, though, would you apply conditional formatting to the entire column?

Well, the idea is that first I need to highlight the account numbers in the ExampleFile1 column C that are not represented the ExampleFile2 column C. After that I need to highlight the account balances in ExampleFile1 column H that are different from the account balances in ExampleFile2 column H for the account numbers that ARE REPRESENTED in both files.
The main purpose of formatting the entire column H in ExampleFile1 is to see which accounts represented in both files have balances that are different from file to file.

LocalRange and ForeignRange need refer to ranges on the sheet where the validation is, but they cannot be names with sheet scope on another sheet (they can be names with workbook scope).

So you are saying that ForeignRange name is not working because it refers to the range of cells in another file? Is there a way around it?
The thing that confuses me the most is that everything works fine when I type in the same formulas and assign the same names manually.

Thank you very much for your assistance!

Eugenia.

[eyerukhi]

I am sorry, should I start a new thread for this problem?

Thanks,
Eugenia.

[eyerukhi]

O.K. I have learned another rule of the forum – don’t leave your thread unattended, right? And I apologize for doing that.

Eugenia.

[eyerukhi]

Bump no response.

[eyerukhi]

Never mind, I found the mistake

Thanks.