Force cell to update when formula causes cell data to change-CodePudding

I am using VBA code to change the background of a cell to be the color of the hex code in the cell, from this other answer:

Set an automatic color background depending on the HEX value in the cell?

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Dim rng As Range, clr As String
    For Each rng In Target
        If Len(rng.Value2) = 6 Then
            clr = rng.Value2
            rng.Interior.Color = _
              RGB(Application.Hex2Dec(Left(clr, 2)), _
                  Application.Hex2Dec(Mid(clr, 3, 2)), _
                  Application.Hex2Dec(Right(clr, 2)))
        End If
    Next rng

bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

The problem is that the background color only updates when you select the cell and press enter. I would like to use formulas to change the hex codes, like: |A |B |C |D | |--|--|--|-----------------| |FF|00|FF|=CONCAT(A1,B1,C1)| I want to modify cell A1 and change the red value of the hex code, and have the correct color show in D1. Right now, I have to select the cell D1 with the formula and press enter before the background color updates.

CodePudding user response：

You can use UDF in Excel. Providing following VBA in a module, the function is available in each workbook of that file. Thus =hexbgcolor("aa00f0") returns the string and colors the back of this cell with that color. A function call like =hexbgcolor(CONCAT(A1,B1,C1)) is also possible and the VBA script runs each time a value changes.

The big issue is that the UDF does not allow to modify any cell. Therefore, a helper function is needed and this function has to be called with Evaluate .

Private Sub hexbgcolor_cell(sheet, row, column, clr)
    On Error GoTo bm_Safe_Exit
    With Sheets(sheet).Cells(row, column)
        If Len(clr & "") = 6 Then
        .Interior.Color = _
                RGB(Application.Hex2Dec(Left(clr, 2)), _
                Application.Hex2Dec(Mid(clr, 3, 2)), _
                Application.Hex2Dec(Right(clr, 2)))
        Else
        .Interior.Color = RGB(255, 255, 255)
        End If
    End With

bm_Safe_Exit:
  
End Sub


Function hexbgcolor(clr As String) As String

    With Application.Caller
        Evaluate "hexbgcolor_cell(""" & .Worksheet.Name & """," & .row & "," & .column & ",""" & clr & """)"
    End With
    
    hexbgcolor = clr

End Function