Hello to everybody,
I am new on this forum.

I would greatly appreciate if somebody would give me an idea (or a
piece of code) about how to make the colors on a graph in Excel to vary
based on value (points) conditions using a VB code. Thank you very much.


--
drumerboy
------------------------------------------------------------------------
drumerboy's Profile: http://www.excelforum.com/member.php...o&userid=30468
View this thread: http://www.excelforum.com/showthread...hreadid=501327

Hi,

Have a look at Jon Peltier's explanation of conditional charts.
http://peltiertech.com/Excel/Charts/...nalChart1.html

Cheers
Andy

drumerboy wrote:
Hello to everybody,
I am new on this forum.

I would greatly appreciate if somebody would give me an idea (or a
piece of code) about how to make the colors on a graph in Excel to vary
based on value (points) conditions using a VB code. Thank you very much.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

I already know that explanation..the problem is.. it is not VB.
Thank you anyway Andy.


--
drumerboy
------------------------------------------------------------------------
drumerboy's Profile: http://www.excelforum.com/member.php...o&userid=30468
View this thread: http://www.excelforum.com/showthread...hreadid=501327

Sorry, my misread.

Try something like this on a column chart. Values below 3 in red and
over 8 in green.

Sub ColorColumns()

Dim vntValues As Variant
Dim intSeries As Integer
Dim intPoint As Integer

With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
vntValues = .Values
For intPoint = 1 To .Points.Count
If vntValues(intPoint) < 3 Then
' red column
.Points(intPoint).Interior.Color = _
RGB(255, 0, 0)
ElseIf vntValues(intPoint) 8 Then
' green column
.Points(intPoint).Interior.Color = _
RGB(0, 255, 0)
End If
Next
End With
Next
End With

End Sub

drumerboy wrote:
I already know that explanation..the problem is.. it is not VB.
Thank you anyway Andy.



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Many thanks Andy,
but there is still a problem I receive a " run time error 91 Object
variable or with block variable not set" message. Which could be the
cause?
ionut


--
drumerboy
------------------------------------------------------------------------
drumerboy's Profile: http://www.excelforum.com/member.php...o&userid=30468
View this thread: http://www.excelforum.com/showthread...hreadid=501327

One more thing, I have tried this code but it only change the color t
the the first point it finds. Why does't goes on to the next point?
am courious to know which is my mistake. I also receive a run-tim
error '1004'

Dim i As Integer
Sub macro1 ()
ActiveSheet.Select
For i = 1 To 100
If Cells(i, 1) 7 Then
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(i).Select
With Selection
.MarkerBackgroundColorIndex = 9
.MarkerForegroundColorIndex = 2
.MarkerStyle = xlCircle
.MarkerSize = 5
.Shadow = False
End With
End If
Next
End Su

--
drumerbo
-----------------------------------------------------------------------
drumerboy's Profile: http://www.excelforum.com/member.php...fo&userid=3046
View this thread: http://www.excelforum.com/showthread.php?threadid=50132

A few comments

When you use the With / End With Construct you need to "attach" objects to
the "With whatever" with a dot, you haven't done that

No need to select either the chart or the series or points in each loop.

What about marker properties that don't meet your condition.

I assume you have a reason for looping cells rather than series values as
Andy suggested, I take it your condition is relates to values in column A,
not the series values.

A guess as to what you are trying to achieve -

Sub test2()
Dim i As Long
Dim p As Long
Dim cht As Chart

On Error Resume Next
Set cht = ActiveSheet.ChartObjects("Chart 3").Chart
If cht Is Nothing Then
MsgBox "no chart named on this sheet named Chart 3"
Exit Sub
End If
On Error GoTo 0

With cht.SeriesCollection(1)
If .Points.Count < 100 Then
MsgBox "Series 1 not 100 points"
Exit Sub
End If

'reset to defaults
.MarkerStyle = xlMarkerStyleAutomatic
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerSize = 3

'as you have 100 points maybe instead
'.MarkerStyle = xlMarkerStyleNone

For i = 1 To 100
p = p + 1
If ActiveSheet.Cells(i, 1) 7 Then
With .Points(p)
.MarkerBackgroundColorIndex = 9
.MarkerForegroundColorIndex = 2
.MarkerStyle = xlCircle
.MarkerSize = 6
End With
End If
Next
End With
End Sub

Use of the "p" counter would allow you to loop say cells (say) 2 to 101

Regards,
Peter T

"drumerboy" wrote
in message ...

One more thing, I have tried this code but it only change the color to
the the first point it finds. Why does't goes on to the next point? I
am courious to know which is my mistake. I also receive a run-time
error '1004'

Dim i As Integer
Sub macro1 ()
ActiveSheet.Select
For i = 1 To 100
If Cells(i, 1) 7 Then
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(i).Select
With Selection
MarkerBackgroundColorIndex = 9
MarkerForegroundColorIndex = 2
MarkerStyle = xlCircle
MarkerSize = 5
Shadow = False
End With
End If
Next
End Sub


--
drumerboy
------------------------------------------------------------------------
drumerboy's Profile:
http://www.excelforum.com/member.php...o&userid=30468
View this thread: http://www.excelforum.com/showthread...hreadid=501327

It works Peter, it works.

The reason I looped cells was that those cells were represented on
the graph but I didn't know how to refer directly to the series of
point
on the graph. Sorry for not beeing explicit. I have numbers on the
first
collum represented on a graph and I have to make signs on the graph
if certain condition about the the data on the first column are met.
But now it works this is important. Thank you very much!
Ionut


--
drumerboy
------------------------------------------------------------------------
drumerboy's Profile: http://www.excelforum.com/member.php...o&userid=30468
View this thread: http://www.excelforum.com/showthread...hreadid=501327

The reason I looped cells was that those cells were represented on
the graph but I didn't know how to refer directly to the series of
point on the graph.

Use the method Andy demonstrated. Assign .Values (above we have "With
cht.SeriesCollection(1)" ), and loop each value in the array.

Amend

For i = 1 To 100
p = p + 1
If ActiveSheet.Cells(i, 1) 7 Then
With .Points(p)

Dim vntValues As Variant

vntValues = .Values
For p = 1 to .Points.count
if vntValues(p).value 7 Then
With .Points(p)

Alternatively

Dim v as Variant

For Each v In .Values
p = p + 1
If v 7 Then
With .Points(p)


Regards,
Peter T

"drumerboy" wrote
in message ...

It works Peter, it works.

The reason I looped cells was that those cells were represented on
the graph but I didn't know how to refer directly to the series of
point
on the graph. Sorry for not beeing explicit. I have numbers on the
first
collum represented on a graph and I have to make signs on the graph
if certain condition about the the data on the first column are met.
But now it works this is important. Thank you very much!
Ionut


--
drumerboy
------------------------------------------------------------------------
drumerboy's Profile:
http://www.excelforum.com/member.php...o&userid=30468
View this thread: http://www.excelforum.com/showthread...hreadid=501327

Andy and Peter,
Many thanks to both of you,
Ionut


--
drumerboy
------------------------------------------------------------------------
drumerboy's Profile: http://www.excelforum.com/member.php...o&userid=30468
View this thread: http://www.excelforum.com/showthread...hreadid=501327