Automatic change multiple cells with items from various dropdown lists and fill background color

[Willow350]

Needing help please!


I have researched and tried various suggestions of conditional formatting and nothing is working. I'm at a lost here. Not sure if this is something that can be accomplished with conditional formatting or vb which I am not good at.....


I have various dropdown lists that I am pulling data from to be plotted in different cells of the spreadsheet, for a better explanation of what I am trying to do I have a worked book that can be viewed at https://1drv.ms/x/s!Av1HA2E92ynlggtjmgExdhfmzMTa.

Thank you all for your help.

 

[My Aswer Is This]

I never click on links.
You should explain here with details what your wanting to do.

 

[Willow350]

Thank you for replying back to my post. Below is the explanation the best way that I can relay it. Automatic change certain cells when an item is selected from a drop down list and also the background fill color has to match the selection of the drop down list.

1st:

Example:

If I choose "C" License from a drop down list of cells in C5:C8

It should automtically change the cells in B20, F20, J20, N20, R20 & V20 with the input of "C" License on each of those cells.

All these cells now will show "C" License with the background fill color to match that "C" license which is organge fill color.

2nd:

Example:

If I choose 3 Years form a dropdown list of cells in G5:G8

It should automaticly change the cells in B22, F21, J21, N21, R21 & V21 with the input of 3 Years on each of those cells.

All these cells now will show 3 Years with the background fill color to match that 3 Years which is gray fill color.

3rd:

Example:

Cells B23:X26 are manual enteries. When entering the manual enteries in B23:X26 the cells background fill color should match the fill color of what was choosen from the dropdown list of G5:G8 .

If I choose 3 Years cells B23:X26 will have the background fill color of gray to match the background fill color of 3 Years.

<colgroup><col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2218;"> <col width="198" style="width: 149pt; mso-width-source: userset; mso-width-alt: 5632;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 1820;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 1735;"> <col width="181" style="width: 136pt; mso-width-source: userset; mso-width-alt: 5148;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2190;"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 796;"> <col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 1820;"> <col width="213" style="width: 160pt; mso-width-source: userset; mso-width-alt: 6058;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 1678;"> <col width="31" style="width: 23pt; mso-width-source: userset; mso-width-alt: 881;"> <col width="59" style="width: 44pt; mso-width-source: userset; mso-width-alt: 1678;"> <col width="292" style="width: 219pt; mso-width-source: userset; mso-width-alt: 8305;"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 1877;"> <col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 739;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1564;"> <col width="295" style="width: 221pt; mso-width-source: userset; mso-width-alt: 8391;"> <tbody> </tbody>

 

[My Aswer Is This]

I do not understand:

If I choose "C" License


Why do you have C in quotes but not License.

Will the cell have "C License" Or "C"

 

[My Aswer Is This]

Here is a example of what you may want.
Not sure what value should be in cell and what value should go in cells.

See if you can read this script and modify to your needs.
Add more If statements if you need more.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C5:C8")) Is Nothing Then
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
            If Target.Value = "C" Then
                Range("B20,F20, J20, N20, R20,V20").Value = "C License"
                Range("B22, F21, J21, N21, R21,V21").Interior.ColorIndex = 15
            End If
    End If
        
     If Not Intersect(Target, Range("G5:G8")) Is Nothing Then
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
            If Target.Value = "C" Then
                Range("B22, F21, J21, N21, R21,V21").Value = "3 Years"
                Range("B22, F21, J21, N21, R21,V21").Interior.ColorIndex = 15
            End If
    End If
End Sub

 

[Willow350]

Please disregard the quotes. It should have never been applied.
Sorry for the confusion.

 

[Willow350]

Thank you for taking the time to write the script for me. I will try it out and keep you posted.

Again thank you for your hard work.

 

[My Aswer Is This]

OK. Maybe you can read the script and know how to change it if needed. It's always best to try and understand what your script is doing. Let me know if you get it working or need more help.

Thank you for taking the time to write the script for me. I will try it out and keep you posted.

Again thank you for your hard work.

 

[Willow350]

I'm not very good with vb trying to learn it so please be patience with me for asking. I have tried to run the code but nothing happens when I try to use it. I copied the script & placed in workbook module, clicked on run. Nothing happens. When I went back to the sheet & tried to do the changes nothing changed/updated.

I read ur script & I understood it. I did not change the cells that the script is referring to. I did not change the worksheet cells neither.

What am I doing wrong or is it that I do not know what I am doing? Please help.

Again thank u for ur help.

 

[My Aswer Is This]

Lets go over this one more time:

We are look for what?
And if we find it what do we put in the cell

For example write your post like this:

If Cell value ="George" Put in range "Jones
If cell value = "Bob" Put in range "Smith"