Bold a bar in a graph (Excel xp)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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[indent]

---

The graph is not “attached” to the rows in excel (for various reasons it can’t be done like this).

---

[/indent]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.

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote