"VBA Code for Excel to Dynamically Change Cell Background Color"

[Del_Pullen]

I'm trying to set the background and font colors of a cell based on the color code associated with the choice selected from a dropdown list.

My situation:

A) The workbook administer enters five eligible status codes (emergency, urgent, routine, delayed, hold) in a column in a helper table and in the adjoining column uses a dropdown list to select a color for each status.  The color options are picked from their own dropdown list (red, green, blue).

B) The eligible status codes are presented to the end user as a dropdown list repeated in cells B1:B100).

C) The user selects a cell (B25), is presented the drop down list, and selects one of the choices ("urgent").

D) The cell displays the user selection "urgent" and the system immediately sets the background color associated with "urgent."

A separate helper table is available to set the RGB code values for the background and font.

     Color "Red"

          StatusCell.Interior.Color = RGB(255,0,0)
          StatusCell.Font.Color = RGB(255,255,255)

     Color "Green"

          StatusCell.Interior.Color = RGB(0,153,0)
          StatusCell.Font.Color = RGB(255,255,255)

     Color "Blue"

          StatusCell.Interior.Color = RGB(47,117,181)
          StatusCell.Font.Color = RGB(255,255,255)

     (The actual table has more color options available.)

I have been able to write enough of the code to change the color if the status (e.g., "urgent") is hardcoded but I don't know how to make it variable, especially not knowing what color belongs to a code without doing a lookup.   

Some guidance would be much appreciated.  Thanks.

[#2 · Accepted solution]

@Del_Pullen 

Without a file (without sensitive data) it is difficult to go into detail in each message.

So forgive me in advance if this is not what you are looking for.

Excel version and operating system and storage medium would also help.

See the attached file for an example.

Hope I was able to help you with this information.

Thank you for your understanding and patience

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@NikolinoDE
THANK YOU. That wasn't exactly what I was looking for but it was exactly what I needed to figure out the missing pieces. Much appreciated,