Jump to content

Welcome to Geeks to Go - Register now for FREE

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
Photo

Excel Chart Colors


  • Please log in to reply

#1
Rhionin

Rhionin

    Member

  • Member
  • PipPip
  • 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?

Thanks for your help.
  • 0

Advertisements


#2
Vino Rosso

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

Jonesey

    Member

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

#4
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 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

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

Rhionin

    Member

  • Topic Starter
  • Member
  • PipPip
  • 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

#7
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
See post #4
  • 0

#8
Rhionin

Rhionin

    Member

  • Topic Starter
  • Member
  • PipPip
  • 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

Jonesey

    Member

  • Member
  • PipPipPip
  • 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

Rhionin

    Member

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

Advertisements


#11
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 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
That way you can address the cells containing your data directly

e.g. for each r in Rows
select each cell in turn (e.g. cells(r,2).select)
Do your colour coding
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

Rhionin

    Member

  • Topic Starter
  • Member
  • PipPip
  • 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

Jonesey

    Member

  • Member
  • PipPipPip
  • 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

Rhionin

    Member

  • Topic Starter
  • Member
  • PipPip
  • 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

#15
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Do....


Loop
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP