Changing cell color to RGB values within Excel spreadsheet using VBA

Question

I've essentially got 10 sets of RGB values in 30 columns (each value in their own cell) with repeated different RGB values in 8 rows below these. What I would like to do is color the cell of 10 cells to the right of these 30 to show a visual representation of the 10 colors (based on their set of RGB values) in all 8 of these rows. I've seen a few VBA examples out in the wild that sort of do something similar but more just on a individual set of RGB values. I was thinking I could create function like the following but that doesn't seem to work. Maybe a Sub routine would be required to do this but not sure where to start. Any help or direction would be appreciated.

Function RGB_Color(R As Integer, B As Integer, G As Integer)

    RGB_Color = Application.ThisCell.Interior.Color = RGB(R, G, B)

End Function

Amen Jlili Amen Jlili · Accepted Answer · 2015-02-07T18:37:55

You can start with this sub. It will color a range of your desire (Rc) with data from (Rs).

Sub ColorRGB(Rs As Range, Rc As Range)
Dim R As Long
Dim G As Long
Dim B As Long
Dim Address(1 To 3) As Long
Dim I As Integer: I = 1
For Each cell In Rs.Cells
Address(I) = cell.Value
I = I + 1
Next
R = Address(1)
G = Address(2)
B = Address(3)
Rc.Interior.Color = RGB(R, G, B)
End Sub

Test:

In this test, we're coloring the cell D1 with the contents from the range A1:C1.

Sub Test()
ColorRGB Sheet1.Range("A1:C1"), Sheet1.Range("D1")
End Sub

Result: enter image description here

The code below uses the RGBColor Sub. It will define a lastrow long (8 in our case) so this can basically handle more rows. Then, it will take the first vertical range of 8 cells and set it in a Range object called r only to do a For Each loop on it later.

With every cell in the For Each loop, we're going to run with another For loop transversely (in direction of columns). The new For loop will run with step of 3 calling our ColorRGB function and telling it to take in the arguments supplied below.

Given the fact that the For loop runs with a step of 3, we've defined a counter for the colored cells called c that increments itself by 1 with each step of the For loop (its step is egal to 3).

I hope this logic is clear. There are obviously better methods to do this.

Sub ColorSheet(sheetname As string)
Dim r As Range
'defining lastrow which is 8
Dim lastrow As Long
With ThisWorkbook.Worksheets(sheetname)
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set r = .Range("A1", "A" & lastrow)
End With
Dim C As Integer: C = 30
For Each cell In r.Cells
For I = 1 To 30 Step 3
ColorRGB ThisWorkbook.Worksheets(sheetname).Range(cell.Offset(0, I - 1).Address(0, 0), cell.Offset(0, I + 1).Address(0, 0)), ThisWorkbook.Worksheets(sheetname).Range(cell.Offset(0, C).Address(0, 0))
C = C + 1
Next I
C = 30
Next
End Sub

This final sub will color the sheet name you provide it with.

Sub Test
ColorSheet("Sheet3")
'And so on...
End Sub

If your sheets are named like SheetX

    Sub Test
    Dim I as Integer
    For I = 1 to 20
    ColorSheet("Sheet"&I)
    Next I
    End Sub

Example:

enter image description here

Changing cell color to RGB values within Excel spreadsheet using VBA

2 Answers