RGB value to cell color

[afgi]

Hi,

can you pls help me with improving my VBA code. the goal is to:
- make excel automatically change cell colors in column D, according to RGB values in column A, B and C
- I want it to be automatic - every time you fill cells in columns A:C, cell in column D will change color based on RGB values
- I do not want it to delate date written in cell in column D

I have managed to make below code, however I need to make it for every row separately.

Sub color()
       
    Dim R As Integer, G As Integer, B As Integer
    R = ActiveCell.Offset(0, -3).Value
    G = ActiveCell.Offset(0, -2).Value
    B = ActiveCell.Offset(0, -1).Value
    
    Range("d1:d20").Interior.color = RGB(R, G, B)
End Sub

example RGB values:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]245[/TD]
[TD="width: 64, align: right"]238[/TD]
[TD="width: 64, align: right"]242[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]149[/TD]
[/TR]
[TR]
[TD="align: right"]253[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]203[/TD]
[/TR]
[TR]
[TD="align: right"]255[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]134[/TD]
[/TR]
[TR]
[TD="align: right"]107[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD="align: right"]203[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]28[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

If you are changing the vaules of cols A:C manually try this

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("A:C")) Is Nothing Then Exit Sub
   With Range("D" & Target.Row)
      .Interior.Color = RGB(.Offset(, -3).Value, .Offset(, -2).Value, .Offset(, -1).Value)
   End With
End Sub

Right click on the sheet tab tab you want this to work on, Click View Code, & paste the above into the window that opens up.

 

[afgi]

Hi,

it does not work... can yoy add some file with code implemented

 

[Fluff]

What does "it does not work" mean? Please remember that I cannot see your screen & no nothing about your data.
Did you follow my instructions regarding where to place the code?
Is you data in cols A:C like

Excel 2013/2016
	A	B	C	D
1	R	G	B	Colour
2	245	238	242
3	32	4	22
4	150	150	149
5	253	205	203
6	255	150	134
7	107	161	104
8	203	59	28
Sheet1

 

[afgi]

What does "it does not work" mean? Please remember that I cannot see your screen & no nothing about your data.
Did you follow my instructions regarding where to place the code?
Is you data in cols A:C like

Excel 2013/2016

	A	B	C	D
R	G	B	Colour

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]245[/TD]
[TD="align: right"]238[/TD]
[TD="align: right"]242[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F5EEF2]#F5EEF2[/URL] , align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=200416]#200416[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]149[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969695]#969695[/URL] , align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]253[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]203[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDCDCB]#FDCDCB[/URL] , align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]255[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]134[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF9686]#FF9686[/URL] , align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]104[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=6BA168]#6BA168[/URL] , align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CB3B1C]#CB3B1C[/URL] , align: right"][/TD]

</tbody>

Sheet1​

pls find file under the link: https://drive.google.com/file/d/1yExDs4ZV0hnzOODYBwsoXoLgg1FKyZBM/view?usp=sharing

 

[Fluff]

If you change any of the values in cols A:C then the corresponding cell in col D will change colour.

 

[afgi]

many thanks, it works

 

[Fluff]

Glad to help & thanks for the feedback

 

[My Aswer Is This]

Works for me