Results 1 to 4 of 4

Thread: Conditional Formatting

  1. #1

    Conditional Formatting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear All

    I am in a fix to solve conditional formatting problem in Excel. Is any Expert available for help. Here is my requirements:

    Conditional Statement #1

    1st for column 1 - Since column 1 is a percentage we need to show a "UP" green arrow to the right of the percentage when the current value of 10 is greater than the previous value of 10.

    Example - The current value is 70.67%, if the previous value of 10 was is the 60s then there would be a green up arrow, if the previous value of 10 was in the 80s then there would be a red down arrow.

    Conditional Statement #2 & #3

    Column 3 and Column 4 are the same. We need to show a "UP" green arrow to the right of the numbers when the current value of 1 is greater than the previous value of 1. In both columns 3 and 4 there can be negative numbers.

    Example for Column 3 - The current value in column 3 is 1.71, if the previous value of 1 was 0.56 then there would be a green up arrow, if the previous value of 1 was 2.13 then there would be a red down arrow.

    Example for Column 4 - The current value in column 4 is 3, if the previous value of 1 was is 1 then there would be a green up arrow, if the previous value of 1 was is 6 then there would be a red down arrow.

    Example for Negative Numbers - The current value is -3, if the previous value of 1 was -5 then there would be a green up arrow, if the previous value of 1 was -2 then there would be a red down arrow.


    Conditional Statement #4

    Column 5 - We need to show 3 Green Up Arrows (next to each other) only when all 3 columns are showing green up arrows. We need to show 3 Red Down Arrows (next to each other) only when all 3 columns are showing red down arrows.

    here is the screenshots/videos:
    http://screencast.com/t/GyZra8kQnhC5
    http://prntscr.com/80qvhz

    here is the file

    https://www.dropbox.com/s/rs4u4rm5jpthe3o/strength.xlsx?dl=0

    please leave update option at the time opening of spreadsheet. For column G This is 1 rule and it needs to be applied to each cell:

    Rule Example

    9x.xx -> 8x.xx = Red Down Arrow 90.08 -> 89.55
    8x.xx -> 9x.xx = Green Up Arrow 89.99 -> 90.12
    RULE | Example

    8x.xx -> 7x.xx = Red Down Arrow | 80.04 -> 79.55
    7x.xx -> 8x.xx = Green Up Arrow | 79.32 -> 80.90

    7x.xx -> 6x.xx = Red Down Arrow | 70.32 -> 69.07
    6x.xx -> 7x.xx = Green Up Arrow | 69.84 -> 70.83

    6x.xx -> 5x.xx = Red Down Arrow | 60.43 -> 59.09
    5x.xx -> 6x.xx = Green Up Arrow | 59.32 -> 60.84

    5x.xx -> 4x.xx = Red Down Arrow | 50.55 -> 49.78
    4x.xx -> 5x.xx = Green Up Arrow | 49.68 -> 50.21

    4x.xx -> 3x.xx = Red Down Arrow | 40.91 -> 39.09
    3x.xx -> 4x.xx = Green Up Arrow | 9.12 -> 40.99

    3x.xx -> 2x.xx = Red Down Arrow | 30.08 -> 29.90
    2x.xx -> 3x.xx = Green Up Arrow | 29.42 -> 30.43

    2x.xx -> 1x.xx = Red Down Arrow | 20.06 -> 19.84
    1x.xx -> 2x.xx = Green Up Arrow | 19.09 -> 20.80

    1x.xx -> 0x.xx = Red Down Arrow | 10.06 -> 09.84
    0x.xx -> 1x.xx = Green Up Arrow | 09.09 -> 10.80


    Regards

    Last edited by fairchance; 2015-08-04 at 05:58 PM.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    I don't think you're going to be in luck.
    It looks like this data is coming in via a DDE link to MetaTrader and I don't think conditional formatting can take into account the previous value in a cell; unless you know different?
    There could be a vba way of doing this but it would be hard work (for a start, the worksheet_change event doesn't trigger with a change in DDE input so you'd have to work with _calculate or some such, and then you wouldn't easily know which cell(s) had been updated) and looking at the rate at which the data appears to be coming in, in the video, vba might not keep up.
    Besides, would I be earning your money for you?

  3. #3
    I have almost done. Please rectify the error only.


    https://www.dropbox.com/s/t0wj1nlmre...3%29.xlsm?dl=0
    http://screencast.com/t/biWjMi0qf

    Regards

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    I have no MT4 stream so I can't test.
    The idea of copying a range to briefly hold past values seems a simple and straightforward one. Ingenious.
    The conditional formatting appears, at first sight, to be fine
    These are some of the things I'd consider:

    1. (a)Your copying of data is triggered when the _calculate event takes place, (b)the copying of data itself triggers a calculation so (c) another _calculate event is triggered (d) ad infinitum. (I got an out of stack space error) So consider doing something with .EnableEvents.
    2. I'm surprised you haven't streamlined the copying code to a couple of lines - all that selecting is redundant, then you wouldn't need the screenUpdating to change.
    3. I'd research the order in which cells are calculated on the sheet, just to make sure that things are being calculated in an appropriate order (this may already be the case).
    4. Look at range.calculate as a possibility?
    5. Is the data coming in too quickly for Excel to cope? Are you simply missing events?
    6. What percentage of your fee do I get?

Posting Permissions

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