Excel Function and Excel Chart Colors

[Samarth]

I am given the task in Excel to complete. I have to make a chart showing 3 different pieces of information :

1. The player's name shown horizontally
2. The player's score shown vertically
3. The player's exposure time to the subject, shown by the color of their bar

Is there any Excel function that is able to do this?? If anyone having idea please help me. Also I simply have to go through and hand-pick the color each individual column one by one??

[Sam.D]

By using the VBA you can use the more conditions like you can set your own case ranges, colour values, and target range. I think that this code will work for you requirement :

Private Sub Worksheet1_Change(ByVal Target As Range)
Dim icolour As Integer
If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Target
Case 0 To 25
icolour = 2
Case 26 To 50
icolour = 3
Case 51 To 75
icolour = 4
Case 75 To 100
icolour = 5
End Select
Target.Interior.ColorIndex = icolour
End If
End Sub

[ThoMas321]

According to me even this code is useful for Excel Chart Colors :
The only thing is you'll have to set the colorindex property yourself :

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Interior
.Pattern = xlSolid
.ColorIndex = 40
End With

ActiveChart.SeriesCollection(1).Points(2).Select
With Selection.Interior
.Pattern = xlSolid
.ColorIndex = 15
End With

ActiveChart.SeriesCollection(1).Points(3).Select
With Selection.Interior
.Pattern = xlSolid
.ColorIndex = 25
End With

[Eric B]

Depending on the value of it's respective "time" cell, if you are making the color every bar on the chart on your spreadsheet then you can use the following :

ActiveChart.SeriesCollection(1).Points(n).Select
With Selection.Interior
.Pattern = xlSolid
.ColorIndex = c
End With

[Zenon]

You can also use this code :

ActiveChart.SeriesCollection(1).Points(3).Select
With Selection.Interior

.Pattern = xlSolid

if cells(1,1).value = 2 then
.ColorIndex = 20
elseif cells(1,1).value = 2 then
.Colorindex = 15
end if
End With

This code has been modified so that the color can be conditional.

[kattman]

The code mentioned by the "Zenon" should work. Also I would like to tell that you will need to write the same code for every bar of the chart, and substitute the point numbers, colorindex and cells(r,c) for whatever's in your spreadsheet. The Cells(r,c) indicates the Row & Column containing the conditions which determine how the bars are colored. According to me you can also use the
Selection.Interior.Colorindex = c
instead of using the With --- End With blocks.