How to update already selected drop-own menu options.

**kerskine** · 12-31-2014, 10:48 AM

Howdy all,

I'm not sure if this is even possible, but I figured this would be a good place to ask since I can't seem to find anything on my own.

I have a drop down menu based on a list and I have used conditional formatting to set each option to a different color background when selected.

Currently, if "Option A" is already selected, and I go to the list and change the name from "Option A" to "Something", the cell with "Option A" already selected loses its background color and continues to show "Option A". And then if I go to the drop-down menu again and select "Something" (where "Option A" used to be), it will say "Something" and the color will be correct.

So, does anyone know of a way that would automatically update the cell with "Option A" to say "Something" and keep the background color without having to reselect it after the name is changed in the list?

Thanks ahead of time,

Kevin

**6StringJazzer** · 12-31-2014, 11:19 AM

First, what is your conditional formatting rule? Does the rule compare the selected value to a cell in the data validation list range? That is, suppose your list is in D1:D5 and you have the dropdown in A1. Does your CF rule look like:

=A1="Option A"

or is it

=A1=D1

In any case, the only way to force the selection to update if you make a change in the list is to use macros. I can help further with that but it would be much easier to do this with your file, rather than starting from scratch and producing a solution that may or may not match your situation.

**kerskine** · 12-31-2014, 11:33 AM

Thanks for the reply Jeff.

The CF rule is =A1=D1.

I was hoping to avoid macros in this workbook but I have already had to use them anyway for a different reason.

I will upload the project shortly so you can take a look at it.

Thanks for the help,

Kevin

**6StringJazzer** · 12-31-2014, 05:41 PM

Here is a VBA solution.

When the user changes a value in the list, that means you have to search for the old value from the validation list and then substitute the new value. THe problem is that after the user changes it, the old value isn't there anymore. Therefore I added a column next to the validation list to capture the "before" values. After the substitution is made, then the macro updates the "before" value to the new value.

The ProjectNames sheet is now protected to prevent accidental updating of the new second column. Column A is still unlocked to make any changes you want.

One condition that may give you a problem is if you delete an existing project name from the list. If you delete it and leave a blank cell, no substitution will be made and the old project name will still be on the first sheet if it was selected.

Please test using realistic scenarios and let me know how it works for you. If you have any issues (not new requirements) I will continue to support the file for a month.

**kerskine** · 12-31-2014, 06:07 PM

Awesome, that is exactly what I needed. I had been on a similar track using a second column with the previous values, but I had done over 100 lines of code and it was still buggy. Thanks so much!