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