BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 12 December 2019
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
November 2019 Newsletter Competition - Winner / Answer
This month's competition was won by Neil Whitten of AXA, to whom a £50 Amazon voucher is winging its way.
The competition asked you to use a list of numbers to colour in a spreadsheet:
Each row gave a cell row and column number, together with the RGB colour to use.
Here's what you got if you followed the instructions:
It's flappy owl!
This image was created by Andy Gould, who created a series of blogs and videos showing how to create a Flappy Owl game in VBA (based on Flappy Bird, if anyone remembers it?).
The obvious way to solve the problem was to write a macro to loop over the rows, processing each in turn. Here was my solution:
Sub CreatePicture()
'the list of instructions
Dim InstructionRange As Range
'the left column of each instruction
Dim InstructionCell As Range
'each cell in turn being coloured
Dim NextCellToColour As Range
'set column of instructions (row 2 down to bottom)
Worksheets("Colour-by-numbers").Select
Range("A1").Offset(1, 0).Select
Set InstructionRange = Range(ActiveCell, ActiveCell.End(xlDown))
'return to picture sheet so can see things happening
Worksheets("Picture").Select
'reset picture
Cells.Clear
'return to picture, and loop over instruction rows
For Each InstructionCell In InstructionRange.Cells
'set the row/column number of the next cell to colour
Set NextCellToColour = Cells(InstructionCell.Value, InstructionCell.Offset(0, 1).Value)
NextCellToColour.Interior.Color = RGB( _
InstructionCell.Offset(0, 2).Value, _
InstructionCell.Offset(0, 3).Value, _
InstructionCell.Offset(0, 4).Value _
)
Next InstructionCell
MsgBox "All done - enjoy the picture!"
End Sub
However, one person filled in the colours manually (I won't reveal their name or what they were meant to be doing at the time!). Another person solved the person correctly, but decided the picture showed a turkey, while a third person submitted the cryptic answer "I tawt I taw a puddy tat".