+ Reply to Thread
Results 1 to 13 of 13

Display a message when a Cell value exceeds 75% of the total allowed expenditure

  1. #1
    Registered User
    Join Date
    07-09-2007
    Posts
    79

    Thumbs up Display a message when a Cell value exceeds 75% of the total allowed expenditure

    How can you display a message when a cell value exceeds 75 % of the allowed expenditure.

    Let us say the Cell in D6 is the Maximum allowed and the Cell D5 is the Rolled up total of expenditure.

    e.g.: If D6 is $100,000 then, when D5 exceeds $75,000 then either make the cell blink or display a message saying: "You have reached the 75% of the allowed expenditure and therefore requires your attention!"

    I frankly could not figure out a way to do that and your help will be much appreciated.

    Thanks in advance!
    Last edited by chamdan; 04-15-2011 at 12:37 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    How is the value getting into Cell D5 ? Keyed In manually , Macro or formual ?

    To change color you could use conditional fomatting or if the value is directly keyed into the cell you could use "data validation"
    Last edited by nimrod; 04-14-2011 at 11:57 PM.

  3. #3
    Registered User
    Join Date
    07-09-2007
    Posts
    79

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    D5 is the accumulated total found in Cell T213, so in D5, you have the following "=T213" but D6 is entered manually. D6 is the allowed expenditure.

    Thanks for your prompt reply Nimrod!

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    you could use "=D5 > D6 * 0.75" in "Conditional Formatting" to turn the cell a bright color .. how about that ? Are you familiar with Conditional Formating ?

  5. #5
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    Condtional Format ( in 2007)

    -- Select cell you want to have conditional format in
    -- Goto Home Tab on top toolbar
    -- click "Condtional formatting" icon
    -- In drop down select "new rule"
    -- in "New Formating" window select "Use a formula to determine ...."
    -- in formula text box copy "=D5 > D6 * 0.75" with NO quotes but include the "=" sign
    -- Now click on "format.." button and choose how you want the conditional format to look

  6. #6
    Registered User
    Join Date
    07-09-2007
    Posts
    79

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    Wow Nimrod! pretty fast in responding appreciate. I tried that but they need a message to popup when such event occurs. I tried to use the CF but they want to see a message instead. I was thinking of causing the cell to blink to attract their attention. I had saved somewhere the function but could not find it. So, my guess I would rather display a message or find a way to make the cell blink until a key is pressed.

    Cheers!

  7. #7
    Registered User
    Join Date
    07-09-2007
    Posts
    79

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    By the way I am using Excel 2003 not 2007 just FYI.

  8. #8
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    Well if you want something real obvious you could use the value in D5 to condtional format the entire sheet ! In other words turn the entire sheet back color a bright red . That might get their attention

  9. #9
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    Another option would be to have a macro triggered by the value in the cells ... this macro would then check value in D5 and display a message when exceed 75%. Though then you have to hope all people have macros enabled .... I prefer Conditional Formatting since it does not required such things as enabled macros.

  10. #10
    Registered User
    Join Date
    07-09-2007
    Posts
    79

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    Question.

    Can a macro be called from inside a Conditional Format. I've tried that but did not work. Is there some kind of Cell change event that can trigger a message when the value of that cell reach this percentage?
    Instead of using the Background effect. I know for sure they would maintain their idea of a popup message.

  11. #11
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    This code , place in the specific sheets code module would do the trick

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    07-09-2007
    Posts
    79

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    Excellent!

    That works for me and I think since they would see this popup they will be happy.

    Cheers and Good night!

  13. #13
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Display a message when a Cell value exceeds 75% of the total allowed expenditure

    Demo of popup ... fill numbers into yellow cell and see errmessage
    Attached Files Attached Files

+ 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