color bar with pattern macro

[Guest]

Hi all
I know how to color excel bar with different colors using color index. How
can
I add pattern with the same color using macro. Do you know what index
number ?
Thanks
Daniel

 

[Guest]

Assuming a simble bar chart with one series, try something like this . . .

Sub FillPatterns()
ActiveChart.SeriesCollection(1).Select
With Selection
.Fill.Visible = True
.Fill.Patterned Pattern:=msoPatternLightUpwardDiagonal
.Fill.ForeColor.SchemeColor = 15
.Fill.BackColor.SchemeColor = 4
End With
End Sub

You can change the pattern type, forecolor, and backcolor.

 

[Guest]

Thanks John
Why we have to create a sub FillPattern(). Does excel macro has the build
in function of fill pattern? I have a sub already and do not want to create
another sub.
in my sub may be I can write

IF
sales > 10000
.Fill.Patterned Pattern:=msoPatternLightUpwardDiagonal

But there are no object for this, it will show error! How can I fix this?
Thanks
Daniel

 

[Guest]

Daniel,

This procedure will change the patterns based on a + / - 10,000 condition:

Sub ChangePatterns()

Dim Cht As Chart
Dim Srs As Series
Dim Pts As Points

Set Cht = ActiveChart
Set Srs = Cht.SeriesCollection(1)
Set Pts = Srs.Points

Cnt = 1

For Each Pt In Srs.Values

'Sales greater than 10000
If Pt > 10000 Then

Srs.Points(Cnt).Select

With Selection
.Fill.Visible = True
.Fill.Patterned Pattern:=msoPatternWideUpwardDiagonal
.Fill.ForeColor.SchemeColor = 42
.Fill.BackColor.SchemeColor = 34
End With

'Sales less than or equal to 10000
ElseIf Pt <= 10000 Then

Srs.Points(Cnt).Select

With Selection
.Fill.Visible = True
.Fill.Patterned Pattern:=msoPatternLightHorizontal
.Fill.ForeColor.SchemeColor = 45
.Fill.BackColor.SchemeColor = 28
End With

End If

Cnt = Cnt + 1

Next Pt

ActiveChart.Deselect

End Sub

I may not be understanding what your wanting. Hopefully this helps.

--
John Mansfield
http://cellmatrix.net

 

[Guest]

Thnks John
You are an expert. Daniel

Daniel,

This procedure will change the patterns based on a + / - 10,000 condition:

Sub ChangePatterns()

Dim Cht As Chart
Dim Srs As Series
Dim Pts As Points

Set Cht = ActiveChart
Set Srs = Cht.SeriesCollection(1)
Set Pts = Srs.Points

Cnt = 1

For Each Pt In Srs.Values

'Sales greater than 10000
If Pt > 10000 Then

Srs.Points(Cnt).Select

With Selection
.Fill.Visible = True
.Fill.Patterned Pattern:=msoPatternWideUpwardDiagonal
.Fill.ForeColor.SchemeColor = 42
.Fill.BackColor.SchemeColor = 34
End With

'Sales less than or equal to 10000
ElseIf Pt <= 10000 Then

Srs.Points(Cnt).Select

With Selection
.Fill.Visible = True
.Fill.Patterned Pattern:=msoPatternLightHorizontal
.Fill.ForeColor.SchemeColor = 45
.Fill.BackColor.SchemeColor = 28
End With

End If

Cnt = Cnt + 1

Next Pt

ActiveChart.Deselect

End Sub

I may not be understanding what your wanting. Hopefully this helps.