Unorthodox VBA question

Janisogor

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi, I would like to ask about the programming language of the VBA. I am trying to create a 100x100 matrix of colorful cells. I have found some instructions on how to fill cells with random RGB colors (see below), however I would like to define only 5 colors which I'd like to randomly distribute over the area. I tried modifiyng the code in multiple ways (defining a new set of xRed1, xGreen1, xBlue1; creating multiple modules and triggering them at once etc.) but none of them worked. Even though I know this is probably a fairly easy task I cannot write code and the VBA language is completely new for me. Thank You all for help.

The code:
Sub KEKE()
Dim rng As Range
Dim WorkRng As Range
Dim xRed As Byte
Dim xGreen As Byte
Dim xBule As Byte
On Error Resume Next
xTitleId = "AAA"
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)
xBule = Application.WorksheetFunction.RandBetween(0, 255)
rng.Pattern = xlSolid
rng.PatternColorIndex = xlAutomatic
rng.Interior.Color = VBA.RGB(xRed, xGreen, xBule)
Next
End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,617
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub Janisogor()
   Dim Cl As Range
   Application.ScreenUpdating = False
   For Each Cl In Range("A1:CV100")
      Cl.Interior.ColorIndex = Application.RandBetween(3, 7)
   Next Cl
End Sub
 

Janisogor

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Thank You for Your reply!

This works fine, however these are Excel's index colors, right? Can I somehow define 5 particular colors and use those?

Thank You again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,617
Office Version
  1. 365
Platform
  1. Windows
Which 5 colours?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,617
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Janisogor()
   Dim Cl As Range
   Dim Ary(1 To 5) As Long
   Ary(1) = 16777215
   Ary(2) = 16185078
   Ary(3) = 2979067
   Ary(4) = 5064263
   Ary(5) = 0
   Application.ScreenUpdating = False
   For Each Cl In Range("A1:CV100")
      Cl.Interior.Color = Ary(Application.RandBetween(1, 5))
   Next Cl
End Sub
 
Solution

Janisogor

New Member
Joined
Mar 2, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Works a charm. Thank You very much again for Your time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,617
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,713
Messages
5,637,936
Members
416,993
Latest member
ant8989

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top