Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Bold a bar in a graph (Excel xp)

    I need to bold a bar in the attached graph. The graph is not "attached" to the rows in excel (for various reasons it can't be done like this). I've attached one chart but i have over 100 in my workbook. Anyway, every place that the word "Total" occurs in the text, i need that corresponding bar to be black on the graph. I am doing them manually now which is very time consuming. Can anyone help me write a macro so it can be done automatically. Thanks a lot. I get so much valuable help from this site. Thanks again
    Attached Files Attached Files

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

    Re: Bold a bar in a graph (Excel xp)

    The attached workbook may not do exactly what you want, but perhaps it'll give you some ideas.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bold a bar in a graph (Excel xp)

    WOWIE! this is so great. Why does it blacken the bar if it's total usa or it just TOTAL? I can't figure that part out in the code. thanks you soooo much!

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

    Re: Bold a bar in a graph (Excel xp)

    Which bars will be colored black is controlled by the instruction

    If InStr(UCase(strVal), "TOTAL") > 0 Then

    This instruction tests whether the string TOTAL occurs somewhere within strVal. strVal is converted to uppercase so that the comparison is not case sensitive. The InStr function returns 0 if the second string is NOT found, and >0 (in fact, the position of of the first character of the second string within the first string) if it is found.

    If you want to make the bar black only if the text begins with TOTAL, use

    If Left(UCase(strVal), 5) = "TOTAL" Then

    If you want to make the bar black only if the text is equal to TOTAL, use

    If UCase(strVal) = "TOTAL" Then

    If you want the comparison to be case sensitive (i.e. TOTAL is OK, Total and total and tOTaL are not OK), use strVal instead of UCase(strVal) in any of the above instructions.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bold a bar in a graph (Excel xp)

    Ok, thank you for the explanation.

    I can't seem to make it work when the range is linked to data on another sheet or in another workbook. What am I missing?
    Attached Files Attached Files

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

    Re: Bold a bar in a graph (Excel xp)

    In your example, the data for the chart are in A3:A19 on the Sheet1 sheet; the names are in A3:A19 on the Chart sheet; so the relative position on the two sheets is the same.

    If you are absolutely sure that this is always the case, it's easy. If not, it'll become much more complicated.

    So let's assume that the data range and the names range have the same address. Replace

    ' Get text in cell to right of value
    strVal = rng.Cells(i, 2)

    with

    ' Get text in cell corresponding to value
    strVal = rng.Cells(i)

    Explanation: rng is the range on the sheet with the chart with the same "address" as the source range of the series.
    In the previous version, this *was* the source range, and the code looked at the cell to the right of the value, because that contained the name.
    Now, rng contains the names, so the code looks up rng.Cells(i).

    Regards,
    Hans

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bold a bar in a graph (Excel xp)

    Ok, now i understand about the .rng.

    I have 4 charts running across my worksheet. The range encompasses A8:A67 and the series is B3:B62, the next chart's range is I3:I62 and the series is E3:E62...
    Range Series
    A8:A67 B3:B62 chart1
    I8:I67 E3:E62 chart2
    Q8:Q67 H3:H62 chart3
    Y8:Y67 K3:K62 chart4

    Then another 4 charts starts at row 89
    A89:A148 B3:B62 chart1
    I89:I148 E3:E62 chart2
    Q89:Q148 H3:H62 chart3
    Y89:Y148 K3:K62 chart4

    Then another 4 charts start at row 169 to 228

    A169:A228 B3:B62 chart1
    I169:I228 E3:E62 chart2
    Q169:Q228 H3:H62 chart3
    Y169:Y228 K3:K62 chart4
    and so on down to row 4326

    There is only one worksheet. Is there anyway to iterate thru each of the charts and make the bar that has "TOTAL" in it's range black. I need a macro because every month this data will change and i don't want to have to manually go in and make the approporiate bar black. Thank you.

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

    Re: Bold a bar in a graph (Excel xp)

    My goodness, you don't believe in making things easy, do you?

    I don't understand why you wanted to refer to another worksheet yesterday. Now you state that there is only one worksheet.

    I'd like to go back to the post that started this thread. There, you mentioned that
    <hr>The graph is not "attached" to the rows in excel (for various reasons it can't be done like this).<hr>
    At first, I didn't question this, and created a solution for you. Now, you keep making it more and more complicated, and I'm starting to think that maybe you should redesign the whole thing. If you just used the names as the X-axis range, it would be much easier.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Bold a bar in a graph (Excel xp)

    Hi Hans,

    I agree with your comments about using the XValues - even if not displayed on the chart they would make it far easier to identify the rows to blacken.

    I had a look at the solution you provided in your first post on this topic and noted there's no code to change a bar back to red if it's no longer a 'total' one. Since the charts are changing each month, this may be important. In that case, you need to change the colouring loop to something like:

    ' Loop through data points
    For i = 1 To ser.Points.Count
    ' Get text in cell to right of value
    strVal = rng.Cells(i, 2)
    ' Color data points red
    Set pnt = ser.Points(i)
    pnt.Interior.ColorIndex = 3
    ' Check if TOTAL occurs in text
    If InStr(UCase(strVal), "TOTAL") > 0 Then
    ' Color data point black
    pnt.Interior.ColorIndex = 1
    End If
    Next i

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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