Change cell color without "Conditional Formatting"

[Keebler]

I have several thousand cells with hex codes (as well as their RGB counterparts).
I need to have a formula that can look at a cell, pull the hex or RGB code and fill the cell with that color. based solely on the hex or rgb code. (A VBA code would be okay as long as it can move from column to column and row to row and put the hex or rgb code in an adjacent cell.
for example

the merged cell on the left is where i need the color displayed

note: when I say several thousand, I literally mean over 8 thousand different hex codes and their RGB compatriots.

 

[Micron]

I think vba is your only option because in order for a cell formula to alter cell colour would be to use CF, and you thread title implies no CF to be used. That would mean looping over rows and applying the colour based on the value in the cell. Might take a few seconds to do that if the loop can be restricted to one column; longer if not. What would trigger this and how often would it have to be run? Once run and if it must run again, can it start from where it left off the last time, or would it have to start from the beginning again?

EDIT - I just noticed that the cell to be coloured is merged. I don't have experience with colouring merged cells in vba but have to imagine it's not all that much more difficult to do.

 

[Keebler]

OR, if one has to use conditional formatting; (argh), is there a way to force conditional formatting to use the fill cell colors from a cell value?

 

[Keebler]

I think vba is your only option because in order for a cell formula to alter cell colour would be to use CF, and you thread title implies no CF to be used. That would mean looping over rows and applying the colour based on the value in the cell. Might take a few seconds to do that if the loop can be restricted to one column; longer if not. What would trigger this and how often would it have to be run? Once run and if it must run again, can it start from where it left off the last time, or would it have to start from the beginning again?

im not opposed to vba, but it would need to cycle thought an entire column (some are several hundred rows long), then jump 4 columns and run the process over and keep doing this whenever data in the upper right cell (of each block of color codes) changes.
the example I uploaded is what each color block looks like, now imagine that I have (currently) about 20 columns (each separated by 3 empty (or nonrelivant ) columns), and each column has a minimum of 20 color "blocks". some have several hundred based on the actual color hues.. example the red column alone has over 250 color blocks

 

[Keebler]

actually, it wont need to change, once the merged cell block has been colored correctly. but when new blocks are added, they need to be colored accordingly as well.
trying to color each of the 8 or 9 thousand cells manually would take years... probably..
new color blocks could be added anywhere in any column if that helps...

 

[Keebler]

OR, is there a way to use a macro to cycle through a series of color blocks then copy the hex color code from a cell, open the fill cell drop down, select "more colors" then the "custom" then select the "Hex" box and copy the (previously copied hex color code) there?

so i made a "macro"
Sub cellcolorchgr1()
'
' cellcolorchgr1 Macro
' look for hex code then change merged cell to that color
'

'
Range("C20").Select
Selection.Copy
Range("A20:A21").Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 4678655
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub


i dont know how to make the range 1 (Range("C20").Select) a variable based on where the mouse or cursor is which would be the range 2 (Range("A20:A21").Select) where I want the color code pasted, and where the mouse or cursor would be resting....

for clarification: range 1 is where the hex color code is; range 2 is the cursor/mouse location that i need colored when triggering the macro
all this in lieu of having a better solution

 

[Keebler]

so i now have figured out how to get the color code shown in the macro..
it is a long color code found by using the RGB color code
(b*(256*256))+(g*256)+r
so in my macro...
((256*256)*71)+(99*256)+255=4678655

so is there a way NOW to use this long code to update the cell color using a dynamic macro?

​

 

[Micron]

All this time away I've been playing with code and came up with this as a start:

Sub ColorMerged()
Dim LRow As Long, i As Long
Dim rng As Range
Dim strRGB As String
Dim aryRGB

LRow = Cells(rows.count, 3).End(xlUp).Row
For i = 1 To LRow Step 2
    Set rng = Range("A" & i).Offset(0, 2)
    strRGB = rng.Offset(1, 0)
    strRGB = Replace(Replace(strRGB, "rgb(", ""), ")", "")
    aryRGB = Split(strRGB, ",")
    Range("A" & i).Interior.Color = RGB(CLng(aryRGB(0)), CLng(aryRGB(1)), CLng(aryRGB(2)))
Next

End Sub

Result and inputs:

I have to take a break. Will review your follow up posts later. Hope you can modify to suit in the meantime. As you might see, there is no validation in that code, so if the rgb values are not valid, it will likely raise an error.

 

[Keebler]

i'll give a check when i get back to the desk, thank you!

 

[Micron]

If you could initiate the code based on a column and not have to process the whole sheet that would help. Bonus is that it would not require figuring out how many columns to move over. That could be a command button on the sheet, or perhaps a double click. I'm thinking that whatever that action is, it raises a range input box, which would be an easy way of selecting e.g. C99 and start from there and process the rest of C after that (because up to C98 has already been done). Trigger the action again, and after choosing e.g. G12, start from there and go down and repeat the sub. Otherwise deciding where to start based on colour would not be reliable.