Code not working on Cells with Formulas

[starbwoy]

Hi Guys-

I have this simple code:

Please Login or Register  to view this content.

I'm targeting cells with formulas so the code doesn't work. If I paste the formulas as values the code works. Is there anyway to leave the formulas and make the code work?

Thanks.

[Leith Ross]

Hello Starbwoy,

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.
3. To do it manually...
Use the Bulletin Board Code Tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.


To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

[starbwoy]

Thanks. I will remember that for next time.

[Leith Ross]

Hello Starbwoy,

The Value property will retyrn the formula if the cell has one. Since all your comparisons are text, use the Text property of the cell.

Please Login or Register  to view this content.

[mikerickson]

A change in a formulas value will not trigger the change event.

If Sheet1!B1 holds the formula =Sheet2!A1+1

Entering Sheet2!A1 will trigger Sheet2's Change event, change the value of Sheet1!B1, but Sheet1's change event will not trigger.

To put it another way, if a cell holds a formula it will never be the Target of a Change event.

The work-around for that is to test for precident cells. For example, this will show a message when the formula in B1 changes Value

Please Login or Register  to view this content.

[starbwoy]

I think I may be doing something wrong the cells are not changing color automatically. I have to re-past them to change color. Any idea why?

[Leith Ross]

Hello Starbwoy,

You should post your workbook. We can then review all your code. Please remove or change any information that is sensitive or confidential. You should also update your profile to reflect which version or versions of Excel you are using.