Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on

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

Hello,

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.

User generated image
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?

Thanks

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.
2017-11-13_EE_ColorChart.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

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

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.
2017-11-13_EE_ColorChart.xlsm