Bored during COVID lockdown? Here is how you can virtually diamond paint using Excel! For the sake of it, I’ll show how to use a pallet and VBA to allow changing the color fast 🙂
1. Set your model as background. Search for a diamond paint model and use it as background in Excel: Page Layout=> Background and select the picture. It helps if it has a white frame. Also, you can use any photo.
2. Define your pallet as table. On first column add a header and then numbers starting from 0. Select the cells and transform to table with Ctrl+T. On Design=> Table Style, select None (no borders, no colors). Color each table cell with one of the colors matching your picture.
Tip: blank cell in Excel is assimilated to zero so you could use 0 for blank
3. Shrink rows and columns to create a gridline to fit the size of the diamond painting model. In this example I used 4 pixels. Do not include the rows containing the pallet or its column. Select the pallet and change the font to 4 (you have to type 4 and enter, it’s not in the dropdown list).
4. Zoom-in to 300% (in case you sized rows and columns to 4 pixels), and size the first column to align to the edge of the background picture. Go with the first picture that is covered correctly by gridline. You can delimit it with a thick border.
Now, shrink the pallet’s rows as much as you can still see the numbers.
Note: Remember the zoom you used in case you need to zoom in or out to check your work!
5. Lock the pallet on top. Select the first row beneath the pallet and View=> Freeze panes. It will keep your pallet visible since your chosen area is way below.
Now you are ready to diamond paint! It will look something like this:
For the VBA part you need one more thing to make sure any picture can be accommodated: mark the first cell in the area you are “painting”.
Select a cell close to the pallet (let’s say C1) and name it StartCell. Here type the reference of the first cell in the area you will paint (case insensitive).
6. Add the magic button. Open VBA editor (alt+F11) and Insert=> Module and copy the following code:
- Option Explicit
- Sub SetColor()
- Dim rng As Range, cl As Range
- Dim dColor As New Dictionary
- Dim diamond As Boolean
- ' needs reference to Microsoft Scripting Runtime
- Set dColor = GetColorsDictionary
- Set rng = Range("B13").CurrentRegion
- If MsgBox("If you want DIAMOND like picture, select Yes, and for plain color select No." & vbCr & _
- "Please note that diamond takes longer time and the color is revealed only when ready", vbYesNo + vbInformation) = vbYes Then diamond = True
- For Each cl In rng
- If dColor.Exists(cl.Value) Then
- If diamond Then
- Application.ScreenUpdating = False
- With cl.Interior
- .Pattern = xlPatternRectangularGradient
- .Gradient.RectangleLeft = 0.5
- .Gradient.RectangleRight = 0.5
- .Gradient.RectangleTop = 0.5
- .Gradient.RectangleBottom = 0.5
- End With
- With cl.Interior.Gradient.ColorStops.Add(0)
- .Color = 16777215
- .TintAndShade = 0
- End With
- With cl.Interior.Gradient.ColorStops.Add(1)
- .Color = dColor(cl.Value)
- .TintAndShade = 0
- End With
- Application.ScreenUpdating = True
- cl.Interior.Color = dColor(cl.Value)
- End If
- cl.Font.Color = dColor(cl.Value)
- End If
- Next cl
- ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True
- End Sub
- Function GetColorsDictionary()
- Dim i, tbl As ListObject
- Dim rslt As New Dictionary
- Set tbl = Sheets(1).ListObjects(1)
- For i = 1 To tbl.DataBodyRange.Rows.Count
- If Not rslt.Exists(tbl.DataBodyRange(i, 1).Value) Then _
- rslt.Add tbl.DataBodyRange(i, 1).Value, tbl.DataBodyRange(i, 1).Interior.Color
- Next i
- Set GetColorsDictionary = rslt
- End Function
Add a button close to pallet and assign it the SetColor macro.
7. How to “paint”. Either type the color code from the pallet or copy paste the cell containing the code. While you fill in areas, you might see that the color on the pallet doesn’t match the picture. You can change the color on the palet and click on the button.
Note: every time you click the button, the sheet is protected. If you want to make changes, right-click on the sheet’s tab and select Unprotect.
From time to time, you can remove background (Page Layout menu) to see how it will look like. When filling in, remember to keep the same zoom you used to define gridline.
At the end, go to View and uncheck Gridlines, Headings, Formula Bar.
The macro allows to fill in either with plain color or gradient to mimic diamonds. First picture shows a plain color filling.
And this is how it looks with gradient filling:
Hope you enjoy it! If you want to start with this example, download the xlsx file (no macros) and add the VBA part yourself: DiamondPaintingExcel. You can use the same file to load other background picture.