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.

 

[Keebler]

sorry for the delay,

sorry, it took so long to respond.. life happens and i needed to reformat my data in the first column (b) to match your sample. no biggie, but it took time..
dang it, i see that in your example, you have the rgb codes in col (c), not (b). (same error, even after moving the rgb codes to col (c))

issues i found
there was an error
Range("A" & i).Interior.Color = RGB(CLng(aryRGB(0)), CLng(aryRGB(1)), CLng(aryRGB(2)))

 

[Micron]

I went with what your first post indicated; leftmost col is A, A col cells are merged, ff numbers in B, RGB numbers in C. Easy enough to change code references to suit whatever it is that you have.

there was an error

So now there is not thus all OK?

Or there still is and I'm supposed to guess what that is? If there is an issue it is probably due to how you implemented the code I posted because it worked for me. I'm afraid that posting one line does not help to provide any clue or context.

 

[Keebler]

i posted the one line because that is what was highlighted in the debug...( i promise i did not know what else to post - sorry my bad)
by error i mean that the debug came on and the vba code stopped.

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


so, if you could please tell me what i need to modify to get the code to work with my spreadsheet, i would appreciate it
so i have the spreadsheet setup exactly as you showed here in post #8
col (A) has two cells merged, then skips a row and the "color block" continues for each color in the column

the first cell in col (B) next to the top of the merged cell is the color name col (C) in the same row is blank
then in the next row (bottom of the merged cell in col (A). i have the RGB codes in col (B) and in col (C) (i dont need it in both, so which ever is easier)

thank you for helping - i really do appreciate it

note: i did not upload a new image as the the last image in post #11, is how the spreadsheet is currently

 

[Micron]

I'm supposed to guess what that is?

That meant you should provide the error message and number. Post 8 and 11 are not like what I posted and parts of them don't look right. As I mentioned, it works with the layout I posted but there is no validation. Thus if the code encounters something like "Red Colors" and tries to make RGB values out of that it's not going to work as is. Rather than describe what you have, either
a) post a pic, and if text spills into adjacent cells make them wider/taller first otherwise I can't tell if they're merged
b) copy and paste into a post and it will make a table
c) upload a file copy to a shared drive and post a link that allows anyone with the link to download the file without being a member.

It may be that another logical test is needed (e.g. if the cell does not contain the characters rgb( ) then skip the row.

Please post code within code tags (vba button on posting toolbar). I hate reading it when it looks like that.

 

[Keebler]

so, I have very clearly ticked you off and i apologize.
that was not my intention. I am tying to learn here, not make enemies!

 

[Micron]

so, I have very clearly ticked you off and i apologize.

Not at all; I'm just "matter-of-fact" sometimes. If I had a dollar for every time I've asked (and sometimes had to reiterate to the same person) about tags I'd be rich. I'm getting to the point where I open a thread, see a screen full of code just dumped into a post, all left justified, that I won't even bother. It makes no difference to what I get paid here (which is nothing, in case you didn't know) so I spare myself the effort to follow it.

If you can deal with the points I made earlier, we can continue. I still think option c is the best approach if you can manage it.

 

[Keebler]

I would do that, except I need to separate that sheet from the rest of the workbook..
the rest of the workbook is of no consequence to this matter.

so, since we spoke last... I did a simple formula to extract the RGB codes and place them into separate cells (i continued to read more elsewhere about changing the cell background colors (interior)). I found that most codes elsewhere use the RGB codes in separate cells

For clarification purposes (the top row is row 1, etc)
column
A B C D E

 

[Micron]

The problem for me is that you post data as pictures, which I can't copy and I'm not inclined to type all that out manually to have something to work with. So I suggest that you either copy/paste the data or copy the sheet into a new wb and upload it somewhere. The problem with a simple copy/paste is that I won't see what the column headers are. You could probably avoid that issue by using the XL2BB add in.

 

[Keebler]

I did not know or I forgot about the add in, i did install it and it produced the following

RESISTORS.xlsm
	A	B	C	D	E
1
2
3		RED COLORS
4
5		Red	255	0	0
6		#ff0000 | rgb(255,0,0)
7
8		Light Salmon	255	160	122
9		#ffa07a | rgb(255,160,122)
10
11		Light Coral	240	128	128
12		#f08080 | rgb(240,128,128)
13
14		Salmon	250	128	114
15		#fa8072 | rgb(250,128,114)
16
17		Dark Salmon	233	150	122
18		#e9967a | rgb(233,150,122)
19
20		Tomato	255	99	71
21		#ff6347 | rgb(255,99,71)
22
23		Indian Red	205	92	92
24		#cd5c5c | rgb(205,92,92)
25
26		Orange Red	255	69	#VALUE!
27		#ff4500 | rgb(255,69,0)
28
29		Crimson	220	20	60
30		#dc143c | rgb(220,20,60)
HTML COLOR CODES

Cell Formulas
Range	Formula
C5:C30	C5	=IF(ISERROR(FIND("rgb(",B6)),"",VALUE(MID(B6,FIND("(",B6)+1,(FIND(",",B6))-(FIND("(",B6))-1)))
D5:D30	D5	=IF(ISERROR(FIND("rgb(",B6)),"",VALUE(MID(B6,FIND(",",B6)+1,(FIND(",",B6,FIND(",",B6)+1))-FIND(",",B6)-1)))
E5:E30	E5	=IF(ISERROR(FIND("rgb(",B6)),"",VALUE(MID(B6,FIND(",",B6,FIND(",",B6)+1)+1,(FIND(",",B6,FIND(",",B6)+1))-FIND(",",B6)-1)))

ok, so i clearly have no idea how to use the plug in.

I doubt that this is correct.. but Im trying.. failing.. but never quittng.. until i do actually quit..

 

[Micron]

So your rgb codes are mixed in the same cell as your hex codes? That is why it hasn't worked yet. You'd either have to use text to columns (insert 2 columns beside whichever column contains this:

so that you end up with this beside it (you can then delete the original column, or not).

OR, the rgb numbers would have to be found; probably by looking for this in the cell: rgb(240,128,128) and then removing parentheses from what's left. What I already wrote will probably work if you split the values as long as they are in the correct column. Otherwise the code would have to be modified to work with the column they end up in. If you do split, copy the sheet first in case you mess that up.

I don't know whether or not you need the hex and rgb values in the same cell or not. Usually it is a bad idea to mix 2 or more representations of the same thing in the same cell. If you need one and not the other, it is more difficult to work with mixed values.