Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel as Graph (Excel 2003)

    Hello

    I was wondering if you can make Excel to behave like a Graph without plotting a graph.

    I attach a small workbook to illustrate. If I change the figures in the yellow column then I want the blue rows to expand or contract accordingly.

    I am not sure if this type of thing was discussed before since my searches do not come up with results.

    Any help or ideas will be appreciated

    Regards

    Kobus
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel as Graph (Excel 2003)

    You can do it with conditional formatting (see attached)

    Steve
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel as Graph (Excel 2003)

    Another option is to use in a cell something like:
    =REPT("n",B3)

    Change the font to "WingDings" and then change the text color. This involves less setup ...

    Steve

  4. #4
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel as Graph (Excel 2003)

    Steve

    Thank you for the quick reply, both are perfect, much appreciated

    Regards

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel as Graph (Excel 2003)

    An extension of this idea, for those interested is to plot different colors to indicate whether the value is Normal, caution area or an alarm value

    I have 2 possibilities, one where the entire bar is the color based on the max, and the other where you have a tricolored bar depending on which region you are in...


    Steve
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel as Graph (Excel 2003)

    Steve

    This is brilliant, I will definately apply this.

    Regards

  7. #7
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel as Graph (Excel 2003)

    Steve

    I have one more question:

    If I want to use the formula in code then I get an error: the line of code is:

    wsNew.Range("L16:L" & (n - 1)).FormulaR1C1 = "=rept("n",RC10)"
    I suppose the "n" is causing the problem, how can I solve this?
    and do I have to add another line of code to format column L to Wingdings?

    Regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel as Graph (Excel 2003)

    You have to double the quotes inside the formula:
    <code>
    ... = "=rept(""n"",RC10)"
    </code>
    Then use
    <code>
    wsNew.Range("L16:L" & (n - 1)).Font.Name = "Wingdings"</code>

  9. #9
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel as Graph (Excel 2003)

    Hans

    Thank you very much, The sheets start to look great now.

    How do I change the colour of the font in column L

    Regards

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel as Graph (Excel 2003)

    wsNew.Range("L16:L" & (n - 1)).Font.Color = vbCyan

    Steve

  11. #11
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel as Graph (Excel 2003)

    Steve

    Thank you very much

    Regards

Posting Permissions

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