Altering Fair Isle Charts in Excel

This week I thought I’d write up a little bit of Excel charting trickery I haven’t seen anywhere else on the internet: A simple macro that lets you ‘paint by numbers’.

Excel is pretty handy for making quick and dirty colourwork charts for knitting, what with it being bundled with so many PCs. Open up a new workbook, set the column widths to 2.25 and you have a nice grid for playing around with!

But if you’ve tried this often enough in Excel, you’ll know that altering the colour scheme is kind of a pain. Copying formats around is not something Excel does spectacularly well, even in the newer versions where you can apply styles.

So I like to use this little setup.

I start by making a ‘palette’ where each number has a background colour I want in the pattern. I then build up a number chart of the pattern I want. Then I run my macro: For every number in my palette, it looks through the chart to find that number. When it finds a match, it copies the background colour of my palette number to a cell 17 rows below the matching chart number. The result is a full colour chart transposed 17 rows below my number chart.

So when I want to change the colours in the pattern, all I have to do is change the palette’s background colours and run the macro again! It’s a real time saver if you’re like me and can’t decide on a colour scheme.

Enough waffle. Here’s the code (Please read on for an explanation of how to use it):

Sub Paintbynumbers()
Dim Chart, Palette, c As Range
Set Chart = Range("L9:Z24")
Set Palette = Range("D5:D9")
For Each i In Palette
 For Each c In Chart
 If c.Value = i Then
 i.Copy
 c.Offset(rowoffset:=17).Select
 Selection.PasteSpecial Paste:=xlPasteFormats
 Else
 End If
  Next c
 Next i
 Application.CutCopyMode = False
 End Sub

If you want to use it in an Excel workbook yourself, make sure macros are enabled (check security settings), hit Alt+F11 to open the Visual Basic Editor, click Insert>Module and paste the code into the new window. This should work for any version of Excel from ’97 onwards.

BUT!! Please note the red highlights! These are values you will have to change yourself according your charting needs!!

The first is the cell range of your number chart. The second is the cell range of your palette. The third is the number of rows to offset the colour chart from the number chart by. This third value must be greater than the number of rows in your number chart or the two will get all mushed up together.

Then just select the ‘Paintbynumbers’ macro from the Excel macro menu and you’re away (or add a macro button to the worksheet – the help file will tell you how if you’re stuck). You can generate all the colour schemes you like!