Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

combobox fill color

0

How do you carry the fill color from a vlookup to the drop down combobox.  The fill color in the vlookup is green for negligible, orange for low, yellow for medium and red for high.  If low is selected in the drop down box i want low as text and green background fill color  

Answer
Discuss

Answers

0
Selected Answer

Your question is both unclear and self-contradicting. You ask how to "carry the fill colour from a vlookup to a combo box", but then you seem to describe the opposite, "If low is selected in the drop down I want low as text and green as fill". Of course, Vlookup is not a cell. It's a worksheet function which could be anywhere. With that said, let me try to be helpful.

The ActiveX combo box has two properties which you need to set. In my test I set

  1. "LinkedCell" as "A1"
    and gave this cell a green fill
  2. "ListFillRange" as "J1:J4"
    and filled this range with the words Negligible, Low, Medium and High

Now I set three conditional formatting rules for A1, all of them of the type "Use a formula to determine which cells to format". Check the box "Stop if True" for all of them.

  1. =$A$1=$J$2 and select fill as yellow
  2. =$A$1=$J$3 and select fill as orange
  3. =$A$1=$J$4 and select fill as red

Now, with this setup the word you select in the combo box will appear in A1 and will be coloured according to its urgency.

If this isn't exactly what you want, you can use a variation of it that meets your requirements. For example, you might hide the "LinkedCell" behind the combo box (or make it invisible by its cell formatting) and use its value to colour any other range of cells on your sheet.

You can also assign a named range to the "ListFillRange" and place that range on another sheet which you might like to make hidden. For example, you might call the range "Priority" and write that name into the "ListFillRange" property. In that case you can use a formula like this for the conditional formatting,

=$A$1=INDEX(Priority, 2)
which returns the 2nd value from the list = "Low" in this case or J2.
Discuss

Discussion

Thank for the clarity and the answer
bhfarrell2 (rep: 2) Oct 14, '17 at 4:19 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login