1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Color Code for Scatter Plot

Discussion in 'VBA Macros' started by msharma864512, Jun 5, 2018.

  1. msharma864512

    msharma864512 Member

    Messages:
    130
    I want to provide color code for scatter plot on the basis of matching the certain condition.
    For e.g. if Sector in column C=Manufacturing, then the color should be Yellow, if its A&D then the color should be Blue, if its Chemicals, then the color code should be Green.

    Please help me with the same.

    Attached Files:

  2. msharma864512

    msharma864512 Member

    Messages:
    130
    It would also be very helpful if anyone could also advise how to shade the quadrant by dividing into four on the basis of average value of x and y axis
  3. vletm

    vletm Excel Ninja

    Messages:
    4,415

    Attached Files:

  4. msharma864512

    msharma864512 Member

    Messages:
    130
    This is gud,

    but my dataset is a bit dynamic and the score which you divided into three columns on the basis of their sector, may not work with my actual data, because, my overall score changes after applying certain condition, and it get pulled up from some other worksheet,

    And in many cases, numnber of companies in the list can be more than the list in this spreadsheet and in some case it can be lesser too.

    So, the charts have to be created by defining the range
  5. vletm

    vletm Excel Ninja

    Messages:
    4,415
    msharma864512
    If You wrote (#1):
    I want to provide color code for scatter plot on the basis of matching the certain condition.
    For e.g. if Sector in column C=Manufacturing, then the color should be Yellow, if its A&D then the color should be Blue, if its Chemicals, then the color code should be Green.
    Please help me with the same.
    And now, You write that Your needs are 'a bit different'.
    If You write this (#1) and You think/need that (#4)...
    You'll get this (#1) -
    not that (#4), which You haven't written.
  6. msharma864512

    msharma864512 Member

    Messages:
    130
    Ya I got ur point, my bad,

    But I hope u r getting my requirement here, I want the chart to be dynamic, if there is only one constant data, then ur solution is perfect.

    But as my requirment has change, it may not be of much of the help to me.

    Hence, it would be very kind of u, if u could help me with my requirement

    and keep it under assumption that Scores, in column C will keep on changing, so spliting the data set into three will not help, and even if i break into three, then i would have to use define the range to create the chart, but it should exclude blank cell too.
  7. vletm

    vletm Excel Ninja

    Messages:
    4,415
    msharma864512
    What are Your 'named requirement'? ... has change, not so clear!
    Would there be always at least one Sector ... and what would be max number?
    If You use term 'dynamic' ... You would open Your term.
    If everything should be 'dynamic' then it would be 'amoeba'!
    Why there are those columns F&G? ... just a repetition!
    If something should pull somewhere ...
    You should write what!
    So far, Excel cannot think!
    ... and if someone else should guess ... that would be 50/60 correct or not!
  8. msharma864512

    msharma864512 Member

    Messages:
    130
    Hi,
    I have attached a new spreadsheet, here u will see how the data changes.

    I have mentioned the details in spreadsheet only

    Attached Files:

  9. vletm

    vletm Excel Ninja

    Messages:
    4,415
    msharma864512
    Where is something 'dynamic'?
    Test this more like static ...
    I used my previous reply.
    Please, next times, Your sample file would be more closer to Your needs.

    Attached Files:

  10. msharma864512

    msharma864512 Member

    Messages:
    130
    Sir the dynamic here are the filters, when u try to use the drop down from cell D3:H3, u will see the chart is not working.

    Now try doing the same thing with the charts i created in my version i attached earlier.

    My charts should change according to the filters and if there any blank cells it should not consider them into the charts.
  11. vletm

    vletm Excel Ninja

    Messages:
    4,415
    msharma864512
    Sir the dynamic here are the filters, when u try to use the drop down from cell D3:H3, u will see the chart is not working.
    You should 'tell' to Excel to do something to chart ... as You wish it to do.
    ... and there are many 'non-blank cells' ...
    > Did You check my file?
    If You change those D3:H3 ... what will happen?
  12. msharma864512

    msharma864512 Member

    Messages:
    130
    > Yes, I did, but its giving wrong data
  13. msharma864512

    msharma864512 Member

    Messages:
    130
    Yes there are many blank cells, thats y i chose to define the name for ranges with a functionality to exclude blank, because as and when u click on any dropdown, the list of entries will reduce
  14. vletm

    vletm Excel Ninja

    Messages:
    4,415
    msharma864512
    It gives Your data!
    What is wrong? Can You give few values?
    Not some looooong sentences which won't help!
  15. msharma864512

    msharma864512 Member

    Messages:
    130
    I have attached two screenshot,
    File named Ur chart is the screenshot of the scatter plot created after selecting 2 from dropdown in cell "E3"
    File named my chart is the screenshot of the scatter plot created after selecting 2 from dropdown in cell "E3"

    Now u can compare the two to see the difference of presentation.

    Only difference between urs and my chart is, that my chart doesnt include color coding.

    Attached Files:

  16. vletm

    vletm Excel Ninja

    Messages:
    4,415
    msharma864512
    I've tried to help You.
    I asked: Can You give few values?
    Do You know what is a value? ... numbers, maybe?
    Values gives/creates that chart ... okay?
    > I modified SAME FILE ...
    Now, You can see those values which creates that chart in D/E/F-columns.
    Are those values correct? ... those are same as in B-column!
    > What would be 'Your values'?
    > And if You compare values to chart ... hmm?

    >> Did You take care that eg 'Score Type' is same in both files?
    Screen Shot 2018-06-06 at 11.14.42.png
    ... and just notice that Your 'dynamic' ranges for chart are not correct!
    Those should have same amount of rows!
    Screen Shot 2018-06-06 at 11.18.13.png Screen Shot 2018-06-06 at 11.18.53.png
    ... is Your chart Okay? Can it show correct?

    Attached Files:

    sathishsusa likes this.
  17. p45cal

    p45cal Well-Known Member

    Messages:
    1,244
    See attached.
    Your first change will colour the points.

    Attached Files:

  18. msharma864512

    msharma864512 Member

    Messages:
    130
    Thank you Very much, this was great. My query is solved now.
  19. msharma864512

    msharma864512 Member

    Messages:
    130
    I beleive there must have seem communication gap from my side to state my query. But thank you very much for ur tremendous help. My query is solved now.

Share This Page