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

#16
Rhionin

Rhionin

    Member

  • Topic Starter
  • Member
  • PipPip
  • 66 posts
Thanks, that did it.

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

When I enable the Legend, it shows each person's name and their color, rather than the color and it's respective unit of time.
  • 0

Advertisements


#17
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Try using a Data Table, rather than a Legend.

Chart options > Data Table > Show Data Table
  • 0

#18
Rhionin

Rhionin

    Member

  • Topic Starter
  • Member
  • PipPip
  • 66 posts
Again, thank you so much for your help. Everything works great.

I get a harmless error at the end of the process, after it has properly colored everything:

Run-time error '1004':
Invalid Parameter


Upon pressing 'Debug' it highlights this piece of code:
ActiveChart.SeriesCollection(1).Points(counter - 1).Select
  • 0

#19
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts

Again, thank you so much for your help. Everything works great.

I get a harmless error at the end of the process, after it has properly colored everything:

Run-time error '1004':
Invalid Parameter


Upon pressing 'Debug' it highlights this piece of code:
ActiveChart.SeriesCollection(1).Points(counter - 1).Select



While you're still in debug mode, with the offending line highlighted in yellow, hover your mouse over the 'counter' variable and see what value it's displaying.

Then, take that number and subtract 1 from it.
Is there a corresponding data point to this number?

If not, you've solved your problem and all you need to do is examine your looping and/or variable assignments.
  • 0

#20
Rhionin

Rhionin

    Member

  • Topic Starter
  • Member
  • PipPip
  • 66 posts

Then, take that number and subtract 1 from it.
Is there a corresponding data point to this number?

Yes, in the debug counter = 45, and the last cell is in row 44. So counter - 1 should reference to a filled cell.
  • 0

#21
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Ok, are there 44 data points on your chart that need to be populated?

Don't worry about the cells, it's the number of chart data points that are making the macro fail.

Does the macro colour EVERY data point? i.e. 1 to 44?


Actually, thinking about this a bit more - the macro should fail right at the beginning when counter = 1

This would give ActiveChart.SeriesCollection(1).Points(counter - 1).Select

i.e. the point index is ZERO. Can't happen in Excel. It needs to start at 1.




You could easily prevent the error message from stopping the macro by inserting one of the "on error...." conditions, but it would be nice to have it run properly, without recourse to this.
  • 0

#22
Rhionin

Rhionin

    Member

  • Topic Starter
  • Member
  • PipPip
  • 66 posts
OK I've got the problem figured out:

It was getting stuck on the IsEmpty(ActiveCell) part and looped eternally because the active cell never changes.
Now it's all fixed and everything works 100% perfectly now! Thank you so much, Jonesey. Here is the finished product:
Sub ColorByChange()

'to use this, paste this in an Excel macro command named ColorByChange,
'in order for this to work, the document must be unlocked (unencrypted)
'macros are found in the "developer" tab
'then select the score chart and run the macro

Dim counter As Integer
counter = 1

	'All of this stuff sorts the names in the cells
	Columns("A:C").Select
	ActiveWorkbook.Worksheets("Chart").Sort.SortFields.Clear
	ActiveWorkbook.Worksheets("Chart").Sort.SortFields.Add Key:=Range("B2:B55"), _
		SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
	With ActiveWorkbook.Worksheets("Chart").Sort
		.SetRange Range("A1:C55")
		.Header = xlYes
		.MatchCase = False
		.Orientation = xlTopToBottom
		.SortMethod = xlPinYin
		.Apply
	End With


	'This colors the bars on the chart
	Range("A2").Select

	Do While Not IsEmpty(ActiveCell) = True
	ActiveSheet.ChartObjects("Chart1").Activate
	ActiveChart.SeriesCollection(1).Points(counter).Select
	counter = counter + 1
	
		With Selection.Interior
	
			If Cells(counter, 3).Value = 1 Then
				.Color = RGB(255, 0, 0)
			ElseIf Cells(counter, 3).Value = 2 Then
				.Color = RGB(247, 150, 70)
			ElseIf Cells(counter, 3).Value = 3 Then
				.Color = RGB(255, 255, 0)
			ElseIf Cells(counter, 3).Value = 4 Then
				.Color = RGB(146, 208, 80)
			ElseIf Cells(counter, 3).Value = 5 Then
				.Color = RGB(0, 176, 80)
			ElseIf Cells(counter, 3).Value = 6 Then
				.Color = RGB(79, 98, 40)
			ElseIf Cells(counter, 3).Value = 7 Then
				.Color = RGB(0, 176, 240)
			ElseIf Cells(counter, 3).Value = 8 Then
				.Color = RGB(0, 112, 192)
			ElseIf Cells(counter, 3).Value = 9 Then
				.Color = RGB(33, 89, 104)
			ElseIf Cells(counter, 3).Value = 10 Then
				.Color = RGB(112, 48, 160)
			ElseIf Cells(counter, 3).Value = 11 Then
				.Color = RGB(112, 48, 160)
			ElseIf Cells(counter, 3).Value = 12 Then
				.Color = RGB(112, 48, 160)
			ElseIf Cells(counter, 3).Value > 12 Then
				.Color = RGB(0, 0, 0)
			Else
				.Color = RGB(128, 128, 128)
			End If
			
			.Pattern = xlSolid
		 End With
	
	ActiveCell.Offset(1, 0).Select
	Loop
End Sub

  • 0

#23
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Looks nice Rhionin! :)

Glad it all worked out, and hopefully you've increased your knowledge of VB in the process.
  • 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