+ Reply to Thread
Results 1 to 7 of 7

Change autoshape color based on a cell value that contains a formula

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Change autoshape color based on a cell value that contains a formula

    Is it possible to change the autoshape fill color based on a cell value that contains a formula. I am unable to code it. Can someone help ?

    Brief description of problem:
    I have value in cell A2 changing based on a formula. The issue is when the formula gets the updated value the autoshape does not change color, unless I manually enter the value in the Cell A2 it happens. Can this be automated by a macro/vb code to take the value from cell A2 whenever it gets calculated and changes ?

    Thank you.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,275

    Re: Change autoshape color based on a cell value that contains a formula

    Welcome to the Forum!

    If a cell has a formula, and the result of the formula changes, it will not fire a Worksheet_Change event. However, a value driving the formula will do so. If it's not on the same worksheet, then maybe that's why you're having trouble. That is, if you have a formula in Sheet1!A1

    =SUM(Sheet2!B1:B10)

    changes in the range on Sheet2 will not cause a Worksheet_Change event on Sheet1, even though the result of the formula changes. However, you can look for Worksheet_Change on Sheet2 and have it change the color of the shape on Sheet1.

    It sounds like you have already written some code but are having trouble getting it to work. I suggest attaching your file. Also please describe how you want to determine the color of the shape.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    09-20-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Smile Re: Change autoshape color based on a cell value that contains a formula

    Thank you for your time 6StringJazzer.

    I appreciate your help. I am still not getting it to work. I have attached the worksheet with the notes in it. See if you can throw some light.

    Thanks onec again.
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,275

    Re: Change autoshape color based on a cell value that contains a formula

    OK, here is your problem. As I mentioned above, if you have the following formula in B6

    =B8

    a change to B6 will not trigger the Worksheet_Change event, but a change to B8 will. Your Worksheet_Change code checks to see if the Target is B6, but it will never be true in your scenario. You need to test for

    Please Login or Register  to view this content.
    or any other cells that the formula in B6 refers to. If the formula is not simply an "=" then you can do this:

    Please Login or Register  to view this content.
    and so forth. So you look for a change in B8 to trigger the event, but you look at the value of B6 to figure out what you need to do.

  5. #5
    Registered User
    Join Date
    09-20-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Change autoshape color based on a cell value that contains a formula

    Thank you 6StringJazzer. Got it resolved.

  6. #6
    Registered User
    Join Date
    03-08-2014
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Change autoshape color based on a cell value that contains a formula

    Hi dear,

    Any body can help me.
    My problem is
    I want to change or fill color of shape on the based of cell value without help of VBA?

    thanks in advance.

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,275

    Re: Change autoshape color based on a cell value that contains a formula

    abhilashb4u, please start your own thread. Your question is not a follow-up to the original and also you have request a non-VBA solution in a subforum that is exclusively for VBA questions.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1