Dynamically assigning cell RGB background color using contents of 3 other cells.

[WmBanx]

Hi, I am very new to this forum with this being my first posting. Please be gentle with me.

Currently I'm finding the following simple task very difficult to achieve in Excel.

I'm wanting to dynamically set the RGB background color for cells in Column D to that of RGB values held in columns A,B,C. In other words, I am wanting to avoid achieving this by using Conditional Formatting with custom STYLES.

ie. A1=REDval, B1=GREENval, C1=BLUEval then cell backgroud color D1 = RGB(A1, B1, C1)

Example1:

A1=253, B1=205, C1=203
Then background RGB color of cell D1 to be automatically set to RGB(A1, B1, C1) = RGB(253, 205, 203)

Example2:

Background colour of Cell D1 = RGB("Value of A1","Value of B1","Value of C1")
Background colour of Cell D2 = RGB("Value of A2","Value of B2","Value of C2")
..
..
Background colour of Cell D65535 = RGB("Value of A65535","Value of B","Value of C65535")

I've tried looking for an answer but my keyword searches are either weak or inaccurate.

Any help or a solution would be most appreciated.

Will.

 

[Rick Rothstein]

OK, well can u please write me a list of instructions starting with #1 , so I can follow them and get this right? I would appreciate that!

1) Right-click the tab for the sheet where your RGB numbers will go.

2) Click "View Code" on the popup menu that appears.

3) Copy/Paste Richard's code from Message #4 into the code window that appeared

4) Go to the worksheet whose tab you right clicked in item 1) above.

5) Type a number between 0 and 255 into cell A2, B2 and C2. Once you enter the 3rd number, a color should appear in cell D2.

 

[Lesley8000]

Thank you so so much!

 

[wreded]

This works marvolously for a single column. How would you extend this to work with multiple columns?
Say I have two (or more) sets of RGB values, Columns G:I, Columns L:N; how would I be able to change a cell's internal color using values using this code?

 

[makerbureau]

Hi there. I'd like to follow up with the same question asked a year ago by Extrude72.

I've been able to successfully make the code posted by Richard work for a range. However, I'd like to do multiple ranges using the same code, and I have been unable to figure out how to do that.

Currently have I have columns U, V & W set up to format column L. I'm trying to do the same for X-Y-Z and M, as well as AA, AB, AC for N.

Anyone able to give guidance on how to merge this code for multiple ranges?

Private Sub Worksheet_change(ByVal Target As Range)
Dim rng As Range, cell As Range
Dim i As Long


Set rng = Intersect(Target, Range("D:N"))


If Not rng Is Nothing Then


On Error Resume Next

For Each cell In Target.Columns(1).Cells
If Application.CountA(Range("D" & cell.Row & ":F" & cell.Row)) < 3 Or _
Application.Count(Range("D" & cell.Row & ":F" & cell.Row)) < 3 Then GoTo next_row
Cells(cell.Row, "G").Interior.Color = RGB(Cells(cell.Row, "D").Value, Cells(cell.Row, "E").Value, Cells(cell.Row, "F").Value)


If Application.CountA(Range("H" & cell.Row & ":J" & cell.Row)) < 3 Or _
Application.Count(Range("H" & cell.Row & ":J" & cell.Row)) < 3 Then GoTo next_row
Cells(cell.Row, "K").Interior.Color = RGB(Cells(cell.Row, "H").Value, Cells(cell.Row, "I").Value, Cells(cell.Row, "J").Value)

If Application.CountA(Range("L" & cell.Row & ":N" & cell.Row)) < 3 Or _
Application.Count(Range("L" & cell.Row & ":N" & cell.Row)) < 3 Then GoTo next_row
Cells(cell.Row, "O").Interior.Color = RGB(Cells(cell.Row, "L").Value, Cells(cell.Row, "M").Value, Cells(cell.Row, "N").Value)




next_row:
Next cell
End If








End Sub

 

[Joe4]

makerbureau,

Not sure if you realize, but the post you are replying to is over 3 years old.

 

[ovisergiu]

1) Right-click the tab for the sheet where your RGB numbers will go.

2) Click "View Code" on the popup menu that appears.

3) Copy/Paste Richard's code from Message #4 into the code window that appeared

4) Go to the worksheet whose tab you right clicked in item 1) above.

5) Type a number between 0 and 255 into cell A2, B2 and C2. Once you enter the 3rd number, a color should appear in cell D2.

This code worked in Excel 2013, BUT you have to have an EMPTY sheet. Having my values already entered in the columns, after pasting the code, none happened.
Then, after pasting the code mentioned, and you enter your values in cell, you can see the RGB color being displayed.
Be careful when you save the Book, cause I was asked about Vb and macros.
Thank you very much for the code and for the step-by-step instructions.
2019!