Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

# Excel Chart Colors

### #1 Rhionin Posted 22 July 2009 - 06:03 AM

Rhionin

Member

• Member
• 66 posts
This one's got me stumped.

I just officiated a test, and would like to make a chart showing 3 different pieces of information:
• The person's name shown horizontally <--->
• The person's score shown vertically |
• The person's exposure time to the subject, shown by the color of their bar (ex: white = brand new / black = very experienced, with shades in between)
Is there any Excel function that is able to do this, or do I simply have to go through and hand-pick the color each individual column one by one?

• 0

### #2 Vino Rosso Posted 22 July 2009 - 06:31 AM

Vino Rosso

Visiting Staff

• Visiting Consultant
• 235 posts
You can use Format > Conditional Format
But only three conditions can be used.

Alternatively, more conditions can be used with VBA: (set your own case ranges, colour values, and target range)

```Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolour As Integer
If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Target
Case 0 To 20
icolour = 3
Case 21 To 40
icolour = 6
Case 41 To 60
icolour = 4
Case 61 To 80
icolour = 5
Case 81 To 100
icolour = 7
End Select
Target.Interior.ColorIndex = icolour
End If
End Sub```

• 0

### #3 Jonesey Posted 22 July 2009 - 10:03 AM

Jonesey

Member

• Member
• 335 posts
Hey Vino, can you actually address charts in this way? I thought it was limited to just cells.
• 0

### #4 Jonesey Posted 22 July 2009 - 10:07 AM

Jonesey

Member

• Member
• 335 posts
This should work, but you'll have to set the colorindex property yourself:

ActiveSheet.ChartObjects("Chart 1").Activate

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

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

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

### #5 Vino Rosso Posted 22 July 2009 - 10:27 AM

Vino Rosso

Visiting Staff

• Visiting Consultant
• 235 posts

Hey Vino, can you actually address charts in this way? I thought it was limited to just cells.

Ah, it helps if I read the question properly!
• 0

### #6 Rhionin Posted 23 July 2009 - 07:37 AM

Rhionin

Member

• Topic Starter
• Member
• 66 posts
Wouldn't you know it, the conditional formatting works great on the cells. How can I apply this color scheme to the chart as well?
• 0

Jonesey

Member

• Member
• 335 posts
See post #4
• 0

### #8 Rhionin Posted 24 July 2009 - 07:24 AM

Rhionin

Member

• Topic Starter
• Member
• 66 posts
Ah, I see you're one step ahead of me, haha.

Edit: I answered my original question, which was how to use that equation. So I plugged it in and it colored the first three bars on the chart. How can I make this color every bar on the chart depending on the value of it's respective "time" cell on my spreadsheet?

Thank you for your help. You guys really are smart.

Edited by Rhionin, 24 July 2009 - 07:43 AM.

• 0

### #9 Jonesey Posted 25 July 2009 - 02:43 AM

Jonesey

Member

• Member
• 335 posts
Right, for every bar on your chart, you'll need a few lines of code like this:

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

Where I've put points(n) you need to enter a unique number for each bar, numbered sequentially from 1 to the total no. of bars.

Where I've put colorindex = c, you need to substitute the c for whatever color you want the bar to be. Experiment with different values.

If you want the color to be conditional, you need to modify the code above, to produce something like this:

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

if cells(1,1).value = 2 then
.ColorIndex = 40
elseif cells(1,1).value = 2 then
.Colorindex = 30
end if

.Pattern = xlSolid
End With

Remember, you need to duplicate the above 9 lines for every bar of the chart, and substitute the point numbers, colorindex and cells(r,c) for whatever's in your spreadsheet.

Cells(r,c) indicates the Row & Column containing the conditions which determine how the bars are coloured.

It's not totally necessary to use the With --- End With blocks.
You could simplify it by using this:

Selection.Interior.Colorindex = c

Have fun!
• 0

### #10 Rhionin Posted 27 July 2009 - 10:25 AM

Rhionin

Member

• Topic Starter
• Member
• 66 posts
Does the while loop work in excel? Or is copying the code for each individual bar the only way?
• 0

### #11 Jonesey Posted 27 July 2009 - 03:47 PM

Jonesey

Member

• Member
• 335 posts
Yes, Do While.....End Do loops work in Excel, no problem - I use them all the time, most often when you're working with a variable number of data points, such as a list that can expand or contract.

For a FIXED number of data points, you might want to use a For....Next loop, or a For Each....Next loop

e.g. for each r in Rows
select each cell in turn (e.g. cells(r,2).select)
Then increment your row number by typing r = r + 1
Next r

Substitute columns ( c) for rows ( r) if you want to work with columns (e.g. [b]cells(3,c).select)
• 0

### #12 Rhionin Posted 28 July 2009 - 02:28 AM

Rhionin

Member

• Topic Starter
• Member
• 66 posts
Perfect, it works! Here's what I made.
```Sub ColorByChange()

Dim counter As Integer
counter = 1

Do
counter = counter + 1

ActiveChart.SeriesCollection(1).Points(counter - 1).Select
With Selection.Interior

If Cells(counter, 3).Value = 1 Then
.ColorIndex = 3
ElseIf Cells(counter, 3).Value = 2 Then
.ColorIndex = 46
ElseIf Cells(counter, 3).Value = 3 Then
.ColorIndex = 6
ElseIf Cells(counter, 3).Value = 4 Then
.ColorIndex = 4
ElseIf Cells(counter, 3).Value = 5 Then
.ColorIndex = 50
ElseIf Cells(counter, 3).Value = 6 Then
.ColorIndex = 10
ElseIf Cells(counter, 3).Value = 7 Then
.ColorIndex = 42
ElseIf Cells(counter, 3).Value = 8 Then
.ColorIndex = 41
ElseIf Cells(counter, 3).Value = 9 Then
.ColorIndex = 49
ElseIf Cells(counter, 3).Value = 10 Then
.ColorIndex = 13
ElseIf Cells(counter, 3).Value = 11 Then
.ColorIndex = 13
ElseIf Cells(counter, 3).Value = 12 Then
.ColorIndex = 13
ElseIf Cells(counter, 3).Value > 12 Then
.ColorIndex = 1
End If

.Pattern = xlSolid
End With

Loop Until counter > 45

End Sub```
• I made it a Do loop that repeats specifically 45 times. How might I turn it into a While statement that is flexible?
• Also, is there any way to make a legend out of the colors I've just set?
• Finally, is it possible to color using the RGB scale rather than the Color Index?
Thank you so much for your help! You've saved me a lot of time.

Edited by Rhionin, 28 July 2009 - 02:45 AM.

• 0

### #13 Jonesey Posted 28 July 2009 - 03:07 AM

Jonesey

Member

• Member
• 335 posts

Perfect, it works! Here's what I made.

• I made it a Do loop that repeats specifically 45 times. How might I turn it into a While statement that is flexible?
• Also, is there any way to make a legend out of the colors I've just set?
• Finally, is it possible to color using the RGB scale rather than the Color Index?

By flexible, do you mean simply scanning your data to see where it ends? That could be done using:

`Do While Not Isempty(ActiveCell) = True`

If you assign a legend to the chart, it SHOULD update it's colouring automatically when the macro runs.

Use the following format for assigning RGB colours to Charts

`ActiveChart.SeriesCollection(1).Points(1).Interior.Color = RGB(0, 255, 0)`

In other words, you use the property Color instead of ColorIndex
• 0

### #14 Rhionin Posted 28 July 2009 - 03:36 AM

Rhionin

Member

• Topic Starter
• Member
• 66 posts

By flexible, do you mean simply scanning your data to see where it ends? That could be done using:

`Do While Not Isempty(ActiveCell) = True`
If you assign a legend to the chart, it SHOULD update it's colouring automatically when the macro runs.

Yep, you've got the right idea. How do I end that Do While loop? "End Do" and "End While" both give an error.
• 0

Jonesey

Member

• Member
• 335 posts
Do....

Loop
• 0

### Similar Topics

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users