[Rhionin]

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.

[Advertisements]

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

[Vino Rosso]

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

[Jonesey]

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

[Jonesey]

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

[Vino Rosso]

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!

[Rhionin]

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?

[Jonesey]

See post #4

[Rhionin]

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.

[Jonesey]

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!

[Rhionin]

Does the while loop work in excel? Or is copying the code for each individual bar the only way?

[Jonesey]

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)

[Rhionin]

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.

[Jonesey]

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

[Rhionin]

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.

[Jonesey]

Do....


Loop