VBA code to change the color of cell D1 based on that is enter in cells A1,B1, and C1 in RGB format

[SharmaAntriksh]

Can you please provide a vba Code to change value of cell D1 everytime i change the value of cells A1, B1, and C1 (RGB codes)

A1	B1	C1	D1
255	0	23	Antriksh
0	255	32	John

<tbody>
</tbody>

 

[Scott Huish]

I assume you mean for every row in A:C, not just row 1. I changed the cell background color, but you can change it for font if you want to.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Dim r As Long, g As Long, b As Long
Set d = Intersect(Target, Range("A:C"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In d
    r = Cells(c.Row, 1): g = Cells(c.Row, 2): b = Cells(c.Row, 3)
    Cells(c.Row, 4).Interior.Color = RGB(r, g, b)
Next
Application.EnableEvents = True
End Sub

 

[Max1616]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xRow As Long
Dim Red As Integer
Dim Blue As Integer
Dim Green As Integer

If Not Intersect(Target, Range("A:C")) Is Nothing Then
    On Error Resume Next
    xRow = Target.Row
    Red = Cells(xRow, 1)
    Green = Cells(xRow, 2)
    Blue = Cells(xRow, 3)
    Cells(xRow, 4).Interior.Color = RGB(Red, Green, Blue)
End If
End Sub

 

[SharmaAntriksh]

Thank you the code works, but it doesn't works on a single file, do you know any way i can share with you or here?

 

[Scott Huish]

Did you put the code in the worksheet module of the workbook you want this to happen on and save it as a .xlsm file?

 

[SharmaAntriksh]

Hey Scott, this code is working perfectly for all the files that i have and i also know that this is an event based procedure and will only run when placed in the specific sheet in VBA code window, but this isn't working for a specific file that i have

 

[Scott Huish]

Thank you the code works, but it doesn't works on a single file, do you know any way i can share with you or here?

You could use dropbox or something like it and share the link

 

[SharmaAntriksh]

okay so the VBA code worked now i just wanted to know how can i make the color change on the basis of the values entered in the cells of the columns F,G,H (as per the formula that converts HEX values to RGB vlaues). Below is the link to the file

https://www.dropbox.com/s/wouer10q0m9giaa/AntrikshSharma_LS.xlsm?dl=0

 

[Scott Huish]

Your DEC2HEX can be much simpler because DEC2HEX provides a way to pad out with leading zeros.

=DEC2HEX(A2,2)&DEC2HEX(B2,2)&DEC2HEX(C2,2)

I'm not sure why you want this because the values in Column F through H just duplicate A through C, but just change the range in the code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range, d As Range
    Dim r As Long, g As Long, b As Long
    
    Set d = Intersect(Target, Range("A2:C" & Rows.Count))
    
    If d Is Nothing Then Exit Sub
        Application.EnableEvents = False
    For Each c In d
        r = Cells(c.Row, 6)
        g = Cells(c.Row, 7)
        b = Cells(c.Row, 8)
        Cells(c.Row, 5).Interior.Color = RGB(r, g, b)
    Next

Application.EnableEvents = True

End Sub