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.
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.