[Rhionin]

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.

[Advertisements]

Try using a Data Table, rather than a Legend.

Chart options > Data Table > Show Data Table

[Jonesey]

Try using a Data Table, rather than a Legend.

Chart options > Data Table > Show Data Table

[Rhionin]

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

[Jonesey]

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.

[Rhionin]

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.

[Jonesey]

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.

[Rhionin]

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

[Jonesey]

Looks nice Rhionin!

Glad it all worked out, and hopefully you've increased your knowledge of VB in the process.