Chart Colouring Macros

[CharlieC]

Hi there,

I'm creating a large monthly report with lots of charts and the core problem is: time taken to update the series colourings when everything shifts with the new data. So...

I have a piece of code which now updates the "Makers" /series within the chart according to a colour formatted range. Thats nice. (Code1)

However, I also have a lot of charts based on "Models" and so need another much larger colour formatted range.

Luckily some fabulous person already made that for us: (Code2 - this copies the formatting to the adjacent cell (for the "Model")

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
Dim bytColor As Byte

Set Target = Intersect(Target, Range("A1:B500"))
If Target Is Nothing Then Exit Sub
For Each rngCell In Target
Select Case rngCell.Value
Case "Make1"
bytColor = 3 '
Case "Make2"
bytColor = 4 '
Case "Make3"
bytColor = 5 '
Case "Make4"
bytColor = 6 '
Case Else
bytColor = 0 '
End Select
Range(Cells(Target.Row, 1), Cells(Target.Row, 2)).Interior.ColorIndex = bytColor ' copies the format accross A und B
Next rngCell

End Sub

Now this works wonderfully when typing in the values but Unfortunately I have a list of 1600 Makes and Models and when I paste this into the worksheet with the code not an awful lot happens (Unless of course I start typing :bug: )

What I want to be able to do is paste my lengthy columns into the sheet with the code and for the formatting to do its thing.

For info (and if you can't tell already) I have very little training in VBA which is why I'm probably just missing something simple. Hopefully.

many thanks!

[mdmackillop]

Welcome to VBAX.
Unfortunately, you don't have hundreds of colours to work with
Try

Sub cols()
Dim i
On Error GoTo exits
For i = 1 To 500
Cells(i, 5).Interior.ColorIndex = i - 1
Next
exits:
End Sub

[CharlieC]

Hi MD, Many thanks for the reply and welcome.

That looks lovely but I'll attach this file to clarify. I don't need lots of colours. I need to do this:

Format the Make value and adjoining Model value with the colour for make.

Maybe this helps..

cheers

c

[mdmackillop]

Unfortunately I have a list of 1600 Makes and Models
So you need to clarify what you are after.

[CharlieC]

This is what I wanted to do.
"What I want to be able to do is paste my columns into the sheet with the code and for the formatting to do its thing."

Please have a look at this:
1617

The code formats the MAKE AND MODEL range when the data is typed in but not when I paste the list, or fill the data from another location (= A2 etc).

I can copy and paste each individual Make and the formatting works. Type the value in:fine. But I want it to do this automatically.

I'm sorry if I've not explained it very well. As I said I'm very inexperienced with VBA although proficient with Excel and Access. Its just I need to do a bit of customising here.

(If it helps I promise to take the VBA course mentioned here - I want to know more :bow: )