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
 Selection.PasteSpecial Paste:=xlPasteFormats
 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!


Filed under Guides/How-to's

17 responses to “Altering Fair Isle Charts in Excel

  1. Great tutorial, thank you so much for taking the time to share this. Funny enough, I’m actually in the process of designing a colorwork project right now. This will help loads!

  2. Genius! I create a table myself and have to fill in each square if I want it in colour. Thank you so much for sharing. Now I’m just gonna have to get up the courage to play with the ‘black magic’ side of spreadsheets. 🙂

  3. Thank you for a great tutorial.

  4. Underground Crafter

    You are my hero. Wow. Thanks for sharing!

    • Haha thanks! There are other ways to do this, I’m sure, but I’d hate to think I learnt all that Visual Basic for nothing, so macros it is!

  5. Oh my gosh! I officially now have a geek knitting crush on you! (♥ excel ♥)

  6. Soooo cool! Thank you for sharing!

  7. cary2crafty

    This is awesome! I’m getting ready to teach myself fair isle, and this looks like something I’ll use in the future. Thanks for sharing this!

  8. Just played with this and its amazing, its take out all the guess work! – thanks for sharing.

  9. Noortje

    Hi Ellen,
    Thanks for your clear tutorial! I just made my first colourchart with it. Improving a bit by putting not only backgroundcolour in the palette but also lines around it. Thanks again. Nice!!

  10. Wanda

    I haven’t tried it yet but at least when I get brave enough I know what I’m doing. I am also a bead and this will help a bunch with that. Thank you

  11. Chris

    Found your tutorial this morning and tried it straight away!! I had never used Macros before, so double challenge. It works!!! thank you for sharing this information. I plan to apply to other charting activities such as mosaic and cross stitching!!

  12. Pingback: Taking my own advice | A pile of sheep

  13. Stephhy

    So nice of you to share. Thanks!!!

  14. This is interesting! Thank You!
    I do a lot of stuff with excel too (or more with the open office program) for my patterns.
    You can try to make a screenshot from the grid, open it in MS paint and easy fill the squares with color. Maybe it is faster this way?
    ☻ Petra

  15. slothy

    Or you just use conditional formatting to change the background and font color of all cells containing a certain number to be a certain way…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s