Auto-fill a cell's color based on numeric RGB values and vice versa in Excel


In Excel, is there a way to auto-fill a cell color based on RGB values entered in other cells — and vice versa?

For example, the following screenshot (taken from the attached file) shows a handy color conversion chart in which values entered manually into the yellow-shaded cells in a given row are automatically converted to equivalent values in the other cells in the same row.

In row [1] for example, entering decimal values for R, G & B in the first three cells (columns D, E & F) causes the remaining fields in that row to auto-populate. In the same way, entering hex R, G & B values in columns J, K & L of  row [2] results in the auto-population of the first three fields of that row (columns D, E & F) as well as the remaining fields located between columns N thru R. Rows [3] & [4] produce similar results for manual entries in columns N and P respectively.

The Display Color cells (column H) were of course filled manually for this screenshot but I'm wondering if there's a way to auto-fill a Display Color based on a decimal or hex value entry in the same row (eg row [5]). Conversely, is it possible to have all of the numeric values in a row populate simply by filling the Display Color cell in that row?

I know that with Conditional Formatting (CF), a cell can be filled with a single specific color based on the value in the same or a different cell. However, I don't know of CF being able to apply multiple colors in the same way. But is it doable with VBA?


Edit: I forgot to mention the final column, namely, converting colors between Excel and AutoHotkey (AHK) which was my purpose for creating the chart in the first place.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
It is doable with VBA.

Excel use a color code, for the cells, and that is R + G *256 + B *256^2, so the code for H9 is 204+102*256+255*256^2 = 16737996
To find the RGB values, the color code has to be reversed
Color code C= Range("H9").Interior.Color
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 256^2 Mod 256

To set the cell with the color from the RGB values
Range("H9").Interior.Color = RGB(Range("D9"), Range("E9"), Range("F9"))

Attached sheet change the color in range H9:H37 based of the RGB values in range D9:F37

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeThotUWasAToadAuthor Commented:
This is really great Ejgil. Thank you very much — not only for the functional attachment but also for the formula and your explanation.

I have some follow-up questions but since you have already provided a solution (which was the objective of my OP), I will ask the follow-up questions in a subsequent thread.
WeThotUWasAToadAuthor Commented:

The file seems a bit fickle as I am getting a Run-time error (13) and/or function failure if, for example, I insert/delete rows. Is there a way to modify the code so that it is more stable and the worksheet is more conducive to handling changes?

Thanks, Steve
Ejgil HedegaardCommented:
Error 13 is Type mismatch, so one of the values are not numeric.
Have changed to check for numeric values too.
Changed to work on column H, from row 1 to last row used.
WeThotUWasAToadAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.