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]

so, with a few formulas added I have this

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

Cell Formulas
Range	Formula
C3	C3	=IF(ISERROR(FIND("rgb",B3)),"",RIGHT(B3,LEN(B3)-FIND("rgb",B3)+1))
C5,C32,C29,C26,C23,C20,C17,C14,C11,C8	C5	=IF(B6="","",IF(LEN(HEX2DEC(LEFT(D6,2)))<2,"0"&HEX2DEC(LEFT(D6,2)),HEX2DEC(LEFT(D6,2))))
D5,D32,D29,D26,D23,D20,D17,D14,D11,D8	D5	=IF(B6="","",IF(LEN(HEX2DEC(MID(D6,3,2)))<2,"0"&HEX2DEC(MID(D6,3,2)),HEX2DEC(MID(D6,3,2))))
E5,E32,E29,E26,E23,E20,E17,E14,E11,E8	E5	=IF(B6="","",IF(LEN(HEX2DEC(RIGHT(D6,2)))<2,"0"&HEX2DEC(RIGHT(D6,2)),HEX2DEC(RIGHT(D6,2))))
C6,C33,C30,C27,C24,C21,C18,C15,C12,C9	C6	=IF(B6="","",IF(ISERROR(FIND("rgb",B6)),"",RIGHT(B6,LEN(B6)-FIND("rgb",B6)+1)))
D6,D33,D30,D27,D24,D21,D18,D15,D12,D9	D6	=IF(B6="","",IF(ISERROR(FIND("#",B6)),"",MID(B6,2,6)))

im sure it is not perfect..

BUT, with this vba fragment

Sub cell_chgr()
Range("a5").Interior.Color = RGB(Range("c5"), Range("d5"), Range("e5"))
End Sub

I have successfully changed the cell background to match the rgb codes (granted, they are using the seperate rgb codes in columns (c,d,e), but it worked..

if you could help me figure how to cycle though each color column that would be wonderful..
is there a way to make the cycle variable based on the cursor/mouse posisiton when running the vba code?
for example: if I place the cursor/mouse on g5, have the vba code run on only the rgb codes from columns (i,j,k)

 

[Micron]

Seems like one step forward, two back. You have thousands of cells to do but now you want to use cell selection to manage this? Now your data full of cells with zeros. What was wrong with just doing text to columns like I did?

 

[Keebler]

Im clearly not doing something correctly

it just seems like everything i try to do i get criticized.. I'm wanting help

 

[Micron]

OK, best I drop out if you feel that way. Good luck.

 

[Keebler]

so, i did figure it out.. YOU set me on the right path -so thank you
here is the vba code that i got to work in my case.. (i will update as i learn more)

I left my errors in - so i could go back and see where i goofed (note the comment markers everywhere)

Sub cell_chgr()
Dim rng As Range
'Dim R As Integer, G As Integer, B As Integer
Dim N As Long, K As Long
'Dim hr As String, hg As String, hb As String, BCLR As Long


'Count the rows that are not empty
'N = Range("B1", Range("B1").End(xlDown)).Rows.Count
N = Cells(Rows.Count, 3).End(xlUp).Row

For K = 5 To N Step 3
Set rng = Range("a" & K)
'R = Range("a" & K).Offset(0, 2)
'G = Range("a" & K).Offset(0, 3)
'B = Range("a" & K).Offset(0, 4)
'BCLR = RGB(R, G, B)
'RGB = rng.Offset(1, 1)

Range("A" & K).Interior.Color = RGB(Range("a" & K).Offset(0, 2), Range("a" & K).Offset(0, 3), Range("a" & K).Offset(0, 4))

Next

'Range("a5").Interior.Color = RGB(Range("c5"), Range("d5"), Range("e5"))

End Sub


the last line (after next) is my reference. it worked, so i used what i learned from your code and modified your code and my working "snipit" to fit my workbook .
this worked for the WHOLE column, just as I wanted. so the next step is to modify the variables to work either the whole sheet or ideally where i place the mouse/cursor

so, thank you for steering me in the correct direction. I apologize I was not able to make myself clear on what I was wanting or trying to do, other than learn..

here is the cleaned up vba code

Sub cell_chgr()
Dim rng As Range
Dim N As Long, K As Long

N = Cells(Rows.Count, 3).End(xlUp).Row

For K = 5 To N Step 3
Set rng = Range("a" & K)
Range("A" & K).Interior.Color = RGB(Range("a" & K).Offset(0, 2), Range("a" & K).Offset(0, 3), Range("a" & K).Offset(0, 4))

Next
End Sub

 

[Keebler]

so, with a slight variation (and a LOT of reading)
this is what I came up with...
I added an input box to ask for the column, then simply ran the vba code on that column

here is my vba code

Sub cell_chgr()
Dim rng As Range
Dim N As Long, K As Long
Dim col As Variant

N = Cells(Rows.Count, 3).End(xlUp).Row
col = InputBox("What Column do you want to colorize?", "Column Colorizer")

For K = 5 To N Step 3
Set rng = Range(col & K)
Range(col & K).Interior.Color = RGB(Range(col & K).Offset(0, 2), Range(col & K).Offset(0, 3), Range(col & K).Offset(0, 4))

Next K
End Sub

 

[Keebler]

so I found very quickly that this vba code was wrong

this one works

Sub cell_chgr()
Dim rng As Range
Dim N As Long, K As Long
Dim col As Variant
Dim ws As Worksheet

col = InputBox("What Column do you want to colorize?", "Column Colorizer")

Set ws = ActiveSheet
N = ws.Cells(ActiveSheet.Rows.Count, col).Offset(0, 1).End(xlUp).Row

For K = 5 To N Step 3
Set rng = Range(col & K)
Range(col & K).Interior.Color = RGB(Range(col & K).Offset(0, 2), Range(col & K).Offset(0, 3), Range(col & K).Offset(0, 4))

Next K
End Sub


this one asks for the column, then counts the rows (in that column) which are to be colored (by looking at the next column (which has the rgb code))
then applies both the column and the row count to successfully color each color block according to the correct rgb codes