• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

cell to show choosen RGB color

kds14589

New Member
I'm attempting to get a cell ("L2") to show the RGB color of values I place in Cells H2-I2-J2
Columns H-J are data validation restricted to 0-255 and the range is "H2:J1002"
Cell ("K2") is combined data from H2, I2, J2 and engages only if there are values in ALL three columns.
My worksheet is ShGE03 (I use CodeName so if sheet name is changed it won't affect program)
I have approximately 1000 rows
This code is from an old workbook and is only for one row, but this time it's 1000 rows and I don't want to write it 1000 times and my loops are rudimentary. Help would be appreciated.


Code:
'    Sub color()
    Dim r As Integer, g As Integer, b As Integer
  r = [H2]
  g = [I2]
  b = [J2]
            Range("K2").FormulaR1C1 = "=IF(AND(RC[-3]<>"""",RC[-2]<>"""",RC[-1]<>""""),RC[-3]&"",""&RC[-2]&"",""&RC[-1],"""")"
            Range("k2").BorderAround ColorIndex:=1, Weight:=xlThin
            Range("l2").Interior.color = RGB(r, g, b)
End Sub
 

Attachments

Last edited:

YasserKhalil

Well-Known Member
Try this code
Code:
Sub Test()
    Dim r As Integer, g As Integer, b As Integer, i As Long
    Application.ScreenUpdating = False
        With ActiveSheet
            For i = 2 To .Cells(Rows.Count, "H").End(xlUp).Row
                If .Cells(i, 8) <> "" And .Cells(i, 9) <> "" And .Cells(i, 10) <> "" Then
                    r = .Cells(i, 8): g = .Cells(i, 9): b = .Cells(i, 10)
                    With .Cells(i, 11)
                        .Value = Join(Array(r, g, b), ",")
                        .Offset(, 1).Interior.Color = RGB(r, g, b)
                    End With
                End If
            Next i
        End With
    Application.ScreenUpdating = True
End Sub
 

Marc L

Excel Ninja
According to the attachment a VBA demonstration as a beginner starter :​
Code:
Sub Demo1()
    Dim R&, V
        R = 2
    With ShGE03
        V = .[H1].CurrentRegion.Columns("A:D").Value2
        Application.ScreenUpdating = False
    While V(R, 4) > ""
       .Cells(R, 12).Interior.Color = RGB(V(R, 1), V(R, 2), V(R, 3))
        R = R + 1
    Wend
        Application.ScreenUpdating = True
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 

kds14589

New Member
Try this code
Code:
Sub Test()
    Dim r As Integer, g As Integer, b As Integer, i As Long
    Application.ScreenUpdating = False
        With ActiveSheet
            For i = 2 To .Cells(Rows.Count, "H").End(xlUp).Row
                If .Cells(i, 8) <> "" And .Cells(i, 9) <> "" And .Cells(i, 10) <> "" Then
                    r = .Cells(i, 8): g = .Cells(i, 9): b = .Cells(i, 10)
                    With .Cells(i, 11)
                        .Value = Join(Array(r, g, b), ",")
                        .Offset(, 1).Interior.Color = RGB(r, g, b)
                    End With
                End If
            Next i
        End With
    Application.ScreenUpdating = True
End Sub
works great THANKS YasserKhalil
 
Top