RGB codes

[plettieri]

Hello all,

Looking to extract the RGB color codes from cells that have been either conditional formatted or paint brush with colors. Any suggestions?

Plettieri

 

[Trevor G]

You could try the following for cell colours by creating function and then use it on a sheet.

Function getColor(Rng As Range, ByVal ColorFormat As String) As Variant
    Dim ColorValue As Variant
    ColorValue = Cells(Rng.Row, Rng.Column).Interior.Color
    Select Case LCase(ColorFormat)
        Case "index"
            getColor = Rng.Interior.ColorIndex
        Case "rgb"
            getColor = (ColorValue Mod 256) & ", " & ((ColorValue \ 256) Mod 256) & ", " & (ColorValue \ 65536)
        Case Else
            getColor = "Only use 'Index' or 'RGB' as second argument!"
    End Select
End Function

Sheet formula (unfortunately the addin has stopped working on my laptop)

=getColor(C3,"rgb")
=getColor(C3,"Index")


This is not my code check here:

Formula to find the cell color value (RGB & Color Index Value)

Hi Community,Is there any formula to find the cell colour value (RGB & Color Index Value) in Excel?Following image shows my requirement. Sample for Color..

techcommunity.microsoft.com

 

[MARK858]

To get the colours of Conditional formatting you'll need to use DisplayFormat and a workaround to get the DisplayFormat to work in a UDF. Something like the adaptions in the codes below

Function getColor(Rng As Range, ByVal ColorFormat As String) As Variant
    Dim ColorValue As Variant
 
    Application.Volatile
    ColorValue = Evaluate("myRGB(" & Cells(Rng.Row, Rng.Column).Address() & ")")
    Select Case LCase(ColorFormat)
    Case "index"
        getColor = Evaluate("myIndex(" & Rng.Address() & ")")
    Case "rgb"
        getColor = (ColorValue Mod 256) & ", " & ((ColorValue \ 256) Mod 256) & ", " & (ColorValue \ 65536)
    Case Else
        getColor = "Only use 'Index' or 'RGB' as second argument!"
    End Select
End Function

Private Function myRGB(ByVal myRng As Range) As Double
    myRGB = myRng.DisplayFormat.Interior.Color
End Function

Private Function myIndex(ByVal myRng2 As Range) As Double
    myIndex = myRng2.DisplayFormat.Interior.ColorIndex
End Function

Book1
	A	B	C	D	E
2	46			255, 255, 0	6
3	17			146, 208, 80	43
4	10			166, 201, 236	37
5	22			255, 0, 0	3
6	37			255, 255, 0	6
7	11			146, 208, 80	43
8	42			255, 255, 0	6
9	34			255, 255, 0	6
10	16			166, 201, 236	37
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=getColor($A2,"rgb")
E2:E10	E2	=getColor($A2,"Index")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A17	Cell Value	>28	text	NO

Edit:
Based on a post by @Jaafar Tribak (he got the idea from somewhere else, I'd have to find his post to credit the original poster)

 

[plettieri]

Mark858,

Thanks so much for this ...a great help and works great

plettieri

 

[MARK858]

You're welcome