PDA

View Full Version : Macro showing various errors in my code for charts.



amitb
09-19-2012, 05:12 AM
Hi friends,

I am new to coding. I was trying to make a macro which searches through the charts in a sheet , searches if the chart has any data of Pfizer and then changing them in the chart to blue. So here, I am trying to search if datalabel has Pfizer and then changing the text to blue.
I tried using this code but did not work:-
Public Sub chartFormatting()

Dim CTRYname As String
Dim p As Integer
ivalue As String
Dim l As Integer
Dim rownum As Integer



For p = 1 To 13

CTRYname = ThisWorkbook.Sheets("Country lookup").Range("A1").Offset(p, 0).Value
rownum = wkbCurr.Sheets(CTRYname).Range("AA25").End(xlDown).Row

For s = 1 To rownum

ivalue = wkbCurr.Sheets(CTRYname).Charts(1).SeriesCollection(1).Points(s).DataLabel. Text

If InStr(ivalue, "Pfizer") <> 0 Then

With ivalue
With .Font

.Color = -65536
.TintAndShade = 0
End With

End With
wkbCurr.Sheets(CTRYname).Charts(1).SeriesCollection(1).Points(s).DataLabel. Text = ivalue


End If

Next s

Next p



End Sub

The variables have been declared else where too.
Now it is showing me a lot of errors. Please help and suggest a better way of formatting the text and lines in charts.
My main challenge is to format them only if Pfizer is there.

p45cal
09-19-2012, 01:34 PM
a macro which searches through the charts in a sheetIs this really:
1. multiple charts in a worksheet,
2. multiple chartsheets in a workbook,
3. 1 chart in each worksheet of a workbook?

What is, and where does, the first error occur?

(You can't colour a string variable and put it back, string variables don't have a colour, you have to colour the datalabel text in place.)