How to use the selected value in a combo to determine conditional formatting in Excel

Formatting values to categorize them or get our attention is a common practice in Microsoft Excel. Fortunately, a conditional formatting rule usually does the trick, and the feature is easy to use. Occasionally, you will run into a situation that leaves you scratching your head a bit. For instance, can you conditionally format the selected value in a combo box control? The answer is yes, but you won’t use conditional formatting; you’ll use a VBA procedure. In this article, I’ll show you how to format a combo’s selected value and the contents of a regular cell based on that selection.

Disclosure: TechRepublic may earn a commission from some of the products featured on this page. TechRepublic and the author were not compensated for this independent review.

First, we’ll embed a combo box control. Then, we’ll use a VBA event procedure to change the control’s font color, based on the selected value. Next, we’ll use a conditional formatting rule to format a cell, based on the combo’ selected value.

I’m using (desktop) Office 365 but you can use earlier versions. You can work with your own sheet or download the demonstration .xlsm files. Nothing is this article is applicable to the browser edition.

SEE: 52 Excel tips every user should master (TechRepublic)

How to embed the dropdown in Excel

You could use a data validation control to offer a list, but in this case, an embedded combo box control is superior because you can link the control to a cell. In this section, we’ll embed a combo box control and give it a short static list–by static, I mean the list won’t be dynamic; you’ll update it manually if the list items change.

First, add the list—Standard, Silver, Gold, Platinum, and Diamond, to an out-of-the-way spot. For our purposes, I’ll use J3:J7 so everything’s on the same sheet and visible.

Now, add a combo box control near cell B2 as follows:

1. Click the Developer tab. In the Control group, click the Insert dropdown and then choose Combo Box from the ActiveX Controls section.
2. Embed the control near B2 and resize as necessary. (Later, we’ll link to B2 and I want you to see the linked cell and the control work together.)
3. With the control selected, click Properties in the Controls group.
4. In the resulting Properties sheet, enter B2 as the LinkedCell setting and J3:J7 as the ListFillRange setting (Figure A).
5. Close the Properties sheet.
6. Check the control’s name; in the demonstration file, it’s ComboBox1. You can give it a custom name. Either way, be sure to note the control’s name. You’ll need it later.
7. Click Design Mode in the Controls group so you can use the control.

Figure A

To use the combo, click the dropdown and select any item. In Figure B, you can see that I choose Gold. This is why I wanted you to embed the control near B2–you can see the selected item in the combo update the content of the linked cell, B2. Go ahead and choose different items and watch B2 update accordingly.

Figure B

How to format the combo box

Chances are, you won’t want to format the combo box itself, but you do have some options. To illustrate, we’ll use a short VBA procedure to change the font color. To do so, click Design Mode in the Controls group (Developer tab) if necessary. Then, double-click the control to open the Visual Basic Editor (VBE) to the active sheet’s (Sheet1) module, including a stub for the control’s Change event procedure. Complete the procedure by adding the code shown in Listing A. (Please don’t copy the code from this web page; the VBE will be unable to interpret unseen web characters. You can copy the text into a text editor and then copy it into the module. Or you can find the procedure in the downloadable module file.) Return to the workbook and save it as a macro-enabled workbook (.xlsm) if you’re working with the ribbon version.

Listing A

Private Sub ComboBox1_Change()

(Change control’s font color, based on selected value.)

Dim cbo As ComboBox

Set cbo = ComboBox1 (If you name your control, be sure to update this definition.)

Select Case cbo.Value

Case “Standard”

cbo.ForeColor = vbRed

Case “Silver”

cbo.ForeColor = vbCyan

Case “Gold”

cbo.ForeColor = vbYellow

Case “Platinum”

cbo.ForeColor = vbMagenta

Case “Diamond”

cbo.ForeColor = vbBlue

End Select

End Sub

Back in the worksheet, choose an item from the dropdown’s list. The change won’t be immediately apparent–click somewhere else in the sheet. The one con is that the ForeColor property changes the entire list, not only the selected item, but that might not matter.

There are other properties you can set in the same way, but the chances are what you’ll really want to do is format a value somewhere in the sheet. Because the combo is linked to B2, it’s easier than you might think.

How to format another cell

It’s common to write conditional formatting rules that format a cell based on the value in another cell. It’s not as clear cut with this example, because the conditional value is in a combo box, not a cell. But you don’t have to reference the combo box; you can reference B2, so that problem is quickly solved. That’s why I used a combo box instead of a data validation control.

Our example doesn’t have a data set, so we’ll use F2 as the cell/range to format:

1. On the Home tab, click Conditional Formatting in the Styles group, and choose New Rule from the dropdown list.
2. In the top pane, select the last option, Use a formula…
3. In the bottom pane, enter the following expression:
   =B2=”Standard”
4. Click the Format button, click the Font tab (if necessary), choose red from the Color dropdown, and click OK. Figure C shows the expression and the format. Click OK to return to the sheet.

Figure C

Now, choose Standard from the combo, and the text in F2 will be red, as shown in Figure D. In addition, the selected item in the combo is also red.

Figure D

Repeat the above steps until you’ve added a rule for each color:

• Silver is cyan (bright green); =B2=”Silver”
• Gold is yellow; =B2=”Gold”
• Platinum is magenta (print pink); =B2=”Platinum”
• Diamond is blue; =B2=”Diamond”

After setting a rule for each color, updating the combo will change both the combo and the font color for F2. You don’t have to use the two together. In fact, it’s doubtful you’ll want to format a combo, but now you know how to use a combo’s selected value to format data.