We help IT Professionals succeed at work.

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.
Watch Question

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


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.



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
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.