VideoPhoto

05 May, 2021

Colorful randomized worksheet creations

Using some Excel functions and conditional formatting you can create unusual graphics, backgrounds, images etc. Here are some of my creations.

Labyrinth

I followed these steps to create this example:

  • selected the sheet and set column width to 2
  • entered in cell B2 this formula: =IF(SUM(A1:C1)=INT(RAND()+0.5),1,"")
  • copied the formula to B2:BC30 range
  • used Conditional Formatting to display the icons, namely: selected the range and set formatting rule to show icon when cell value is >=1, and no icon when cell value is <1 and >=0
  • filled the range with green background color

SierpiƄski Triangle

 


This triangle was created as described here:

  • selected the sheet and set column width to 2
  • entered in cell B2 this formula: =IF(SUM(A1:C1)=1,1,"")
  • copied the formula to B2:BR37 range
  • entered "1" in cell AJ3
  • used Conditional Formatting to display the icons, namely: selected the range and set two formatting rules:
  1. Format based on Icon Sets style when cell value is >=1, and
  2. Format only cells containing cell value ="" to selected Color and Pattern

Random Paths

This random pattern has been created, after setting column width to 2, with the following formula:

=IF(SUM(A1:C1)>0,SIN(RAND()*45),COS(RAND()*45))

entered in cell B2 and copied to B2:BC30 range. In Conditional Formatting I used formatting rule with Icon Set, when cell values is>=75 percent,<75 and>=50 percent, and <50 and >=25 percent.

Mosaic

To create a similar mosaic of colors in certain range of quadratic cells, set the column width to 2 and then use the following macro code. You need to copy and paste the code into open VBE space (press ALT+F11 to get there).

Sub RandomColors()
Dim rng As Range
Dim WorkRng As Range
Dim xRed As Byte
Dim xGreen As Byte
Dim xBlue As Byte
On Error Resume Next
xTitleId = "MyColors"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each rng In WorkRng
xRed = Application.WorksheetFunction.RandBetween(0, 255)
xGreen = Application.WorksheetFunction.RandBetween(0, 255)
xBlue = Application.WorksheetFunction.RandBetween(0, 255)
rng.Pattern = xlSolid
rng.PatternColorIndex = xlAutomatic
rng.Interior.Color = VBA.RGB(xRed, xGreen, xBule)
Next
End Sub


No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.