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

[scootty83]

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.

 

[GWteB]

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.

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

 

[scootty83]

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.

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]

Welcome to MrExcel!
Glad to help & thanks for letting me know.

 

[scootty83]

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.

 

[GWteB]

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]

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]

Obviously I've misinterpreted the word unique
If you wanted to change the first line to the second, it should work as requested.

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

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

 

[scootty83]

Obviously I've misinterpreted the word unique
If you wanted to change the first line to the second, it should work as requested.

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

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

You're awesome! Working great now. Thanks!