Apply the format of a cell referenced in a data validation dropdown list.

scootty83

New Member
Joined
Dec 10, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hello all!

On a sheet named 'Lists' I have 62 cells (A1:A62) that I have named 'CatList' in the name manager. Each cell has a unique 1 to 2 character alphanumeric identifier and a unique fill color. It is likely additional identifiers and colors will be added to this list (it has grown from 45 to 62 in the last few days). These represent services a vendor may provide.

On a Sheet named 'ARC Vendor Ratings' there are six columns (Q15:Q500, S15:S500, U15:U500, W15:W500, Y15:Y500, AA15:AA500) that reference 'CatList' in data validation dropdowns. On this same sheet at the top I have copied the cells from 'Lists' to show as a legend of available services. As a user adds or reviews a vendor, they will select a service that a vendor provides in the dropdowns of the columns.

I am familiar with conditionally formatting cells based on a value. In fact, that's exactly what I originally did. However, doing this was very time consuming and tedious. And, once the list grew from 45 to 62 possible services, I had to go back and change all the conditional formatting rules. Also, having this many rules slows down excel anytime I make a change/add a rule. It freezes for 30 seconds or more for each rule I add or change.

Is there a way to make the dropdown list to also format to its referenced cell? And if more identifiers/colors are added/changed, it automatically updates the dropdown list and formatting? (or updates if I add to 'CatList')

I am not well versed with VBA, which I'm pretty sure will be used to accomplish this. So please, break it down Barney style if you can.

Also,
I tried to follow the advice here but was unable to get it to work, but it seems to be close to what I am looking for.
 

Attachments

  • ARC Vendor Ratings .jpg
    ARC Vendor Ratings .jpg
    126.3 KB · Views: 13

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
The code below is likely to do what you want. It goes in a worksheet module and the code is triggered whenever the value of any cell on that particular worksheet is changed.
If that's the case, the code checks if the change took place in one of your columns with the dropdowns in it. If that's the case, the formatting of the cell within the validation list in column A with the (validated) value is copied onto the just changed cell.

In your workbook, right click on the ARC Vendor Ratings worksheet tab and click View Code.
The VBE will open a pane with the (blank) worksheet module. Paste the code below in that pane and close the VBE.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range
    
    With Application
        If Not .Intersect(Target, Range("Q15:Q500, S15:S500, U15:U500, W15:W500, Y15:Y500, AA15:AA500")) Is Nothing Then
            If Target.Count = 1 Then
                On Error Resume Next
                Set c = [CatList].Find(Target.Value)
                On Error GoTo 0
                If Not c Is Nothing Then
                    c.Copy
                    .EnableEvents = False
                    Target.PasteSpecial Paste:=xlPasteFormats
                    .CutCopyMode = False
                    .EnableEvents = True
                End If
            End If
        End If
    End With
End Sub
 
Solution

scootty83

New Member
Joined
Dec 10, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
The code below is likely to do what you want. It goes in a worksheet module and the code is triggered whenever the value of any cell on that particular worksheet is changed.
If that's the case, the code checks if the change took place in one of your columns with the dropdowns in it. If that's the case, the formatting of the cell within the validation list in column A with the (validated) value is copied onto the just changed cell.

In your workbook, right click on the ARC Vendor Ratings worksheet tab and click View Code.
The VBE will open a pane with the (blank) worksheet module. Paste the code below in that pane and close the VBE.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range
  
    With Application
        If Not .Intersect(Target, Range("Q15:Q500, S15:S500, U15:U500, W15:W500, Y15:Y500, AA15:AA500")) Is Nothing Then
            If Target.Count = 1 Then
                On Error Resume Next
                Set c = [CatList].Find(Target.Value)
                On Error GoTo 0
                If Not c Is Nothing Then
                    c.Copy
                    .EnableEvents = False
                    Target.PasteSpecial Paste:=xlPasteFormats
                    .CutCopyMode = False
                    .EnableEvents = True
                End If
            End If
        End If
    End With
End Sub
[/CO
[/QUOTE]
Absolutely fantastic work! This works like a charm. After inserting this code into the VBE, I copied the cells in 'CatList' and pasted just the values into column AA and, boom, it worked perfectly!

Thank you! Thank you! Thank you!
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Welcome to MrExcel!
Glad to help & thanks for letting me know.
 

scootty83

New Member
Joined
Dec 10, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile

ADVERTISEMENT

Welcome to MrExcel!
Glad to help & thanks for letting me know.
Ran into a formatting issue when I put an 'S' or 'H' in a cell within the defined range in the VBA. They format to an incorrect cell and text color.

I double checked the formatting from CatList (see pic). Even re-formatted the two cells to ensure the correct color was assigned to those cells. Then I reselected the range in Name Manager. Then I double checked to see if I had any residual conditional formatting rules that may have been running still, but there are none with those formats.

I could share a sanitized workbook if that'd help, but that'll have to be later today.
 

Attachments

  • CatList.png
    CatList.png
    18.5 KB · Views: 5
  • FormattingError.png
    FormattingError.png
    44.6 KB · Views: 5

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
That's odd behavior indeed, should not happen since the formatting is copied, not just the interior color. Something else must be going on, not sure what exactly.
If you could share a workbook I'll look into it.
 

scootty83

New Member
Joined
Dec 10, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile

ADVERTISEMENT

That's odd behavior indeed, should not happen since the formatting is copied, not just the interior color. Something else must be going on, not sure what exactly.
If you could share a workbook I'll look into it.
So, I added some additional categories in both CatList and in the legend at the top totaling 68 formats, including a blank cell with just a border. In doing this, I changed the order in which the cells were organized. I then noticed that the color formats changed again... So, I went to CatList and sorted A-Z and the color formatting changed once again...

It looks like that if there is a character in an identifier listed before that same character in a single character identifier in CatList, the VBA will format based on the first usage of that character.

For example, 'SH' is formatted with a shade of blue. An 'H' by itself listed further down in CatList is shaded purple. The 'H' by itself when selected in one of those six columns, however, formatted to the shade of blue and font color of the cell with 'SH' in it, which is the first usage of an 'H' in CatList. The first usage of an 'S' in CatList is with 'PS' and is a shade of gray with black font. The single 'S' located lower in CatList is shaded dark red with white font. Again, however, if you select just 'S' in one of the six columns, it will format to how the cell with 'PS' is formatted.

I'm not seeing a way to share the workbook here. Is there a way to get a slimmed-down version to you?

BTW, it looks like there was a similar issue in the thread I referenced above in the OP. But not sure what the VBA would look like to fix it.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Obviously I've misinterpreted the word unique :unsure:
If you wanted to change the first line to the second, it should work as requested.

Set c = [CatList].Find(Target.Value)

Rich (BB code):
Set c = [CatList].Find(Target.Value, LookAt:=xlWhole)
 

scootty83

New Member
Joined
Dec 10, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
  3. Mobile
Obviously I've misinterpreted the word unique :unsure:
If you wanted to change the first line to the second, it should work as requested.

Set c = [CatList].Find(Target.Value)

Rich (BB code):
Set c = [CatList].Find(Target.Value, LookAt:=xlWhole)
You're awesome! Working great now. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,450
Messages
5,596,221
Members
414,046
Latest member
mbeutler1203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top