Changing Cell Color Using Worksheet_SelectionChange and a Module

16 views Asked by At

I have a spreadsheet where I am converting DEC number to HEX number. Beside the HEX number I am running VBA code to change the background of the cell to the appropriate color based on the HEX number. My issue is when I run the color changing code, I acquired in a forum, within the Worksheet, it works great. But if I put the code in a Module and call it from the Worksheet, it bypasses the statement needed to change the cell color.

I know I'm missing something, or maybe trying to run module level code on code that wasn't meant to run anywhere but in the worksheet.

What I want to know is what I need to change to make the code work at the module level.

I like to work efficiently so I want to call the module level code from each sheet, not have the code in every sheet.

I hope this is understandable.

I do not want to use Conditional Formatting.

I tried what I mentioned above.

Worksheet_SelectionChange Event

Private Sub worksheet_selectionchange(ByVal target As Range)
    Call ColorChanges
End Sub

Module Code

Public Sub ColorChanges()
    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Dim rng As Range, clr As String
    For Each rng In target
        If IsEmpty(rng.Value2) Then
            rng.Offset(0, 1).Interior.Color = xlNone
        ElseIf Trim(rng.Value2) = "" Then
            rng.Offset(0, 1).Interior.Color = xlNone
        ElseIf Left(rng.Value2, 1) = "#" And Len(rng.Value2) = 7 Then
            clr = Right(rng.Value2, 6)
            rng.Offset(0, 1).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
0

There are 0 answers