Search code examples
excelvbadynamicbackground-color

Excel Dynamic Fill Cell with unique Color from another Sheet

Where I work, we use somewhere around 1000 unique colors and I was tasked with making these colors visible in our database. I'm already using a macro that converts LAB to RGB and then fills the cell with the unique color in our referential document. The problem is that I want to take the cell that has the unique color and use VBA or a formula to pull that cell color from the referential workbook into each document based on the unique ID of the Color.

I've tried using Vlookup but it doesn't do anything for the fill color. I don't want to resort to conditional formatting because I'm looking at 1000(colors) * 25(excel sheets) for just one workbook.

Solution

  • Here's one way you can do it.

    Basic setup:

    enter image description here

    In a regular module:

    Function GetColor(v)
    Dim m, c As Range, rngDB As Range, clr, f
    
    Set rngDB = Sheet1.Range("B3:C9") 'your lookup table
    Set c = Application.Caller        'the cell calling this function
    m = Application.Match(v, rngDB.Columns(1), 0) 'look for an ID match
    If Not IsError(m) Then
        clr = rngDB.Columns(2).Cells(m).Interior.Color 'fill color from lookup table
    Else
        clr = -1 'no match, so clear any fill
    End If
    f = "SetColor(""" & c.Parent.Name & """,""" & c.Address & """," & clr & ")"
    c.Worksheet.Evaluate f 'use Evaluate to side-step some restrictions on what a UDF can do
    GetColor = ""
End Function

'this just sets the cell fill color
Function SetColor(wsName, addr, clr)
    With ThisWorkbook.Worksheets(wsName).Range(addr)
        If clr >= 0 Then
            .Interior.Color = clr         'set a fill
        Else
            .Interior.ColorIndex = xlNone 'clear the fill
        End If
    End With
End Function

    Limitations of using user-defined functions in VBA:
    https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1#:~:text=A%20user%2Ddefined%20function%20called,Change%20another%20cell's%20value.