Excel VBA using Evaluate() function to turn calling cell to RGB found in target cell
This function sets the CURRENT cell value to an RGB based on the target cell- Uses REGEX 5.5
[✓]Microsoft VBScript Regular Expressions 5.5
Function SetCellColor(X As Range) As String
Dim re As New RegExp
Dim MC As MatchCollection
Dim SM As SubMatches
Dim RGB As String
Dim AC As Range
''What cell is calling: ''https://www.mrexcel.com/board/threads/function-determining-what-cell-it-is-being-called-from.51343/
Set AC = X.Parent.Cells(Excel.Application.Caller.Row, Excel.Application.Caller.Column)
re.Pattern = ("(\d{3}).?(\d{3}).?(\d{3})")
If re.Test(X.Value) Then
Set MC = re.Execute(X.Value)
Set SM = MC.Item(0).SubMatches
RGB = SM(0) & ", " & SM(1) & ", " & SM(2)
RGB = "SetRGB(" & AC.Address(False, False) & ", " & RGB & ")"
Evaluate (RGB)
Else
''no match - no RGB
End If
End Function
''Set RGB value of cell VIA Evaluate (SUB call)
''I.E. Evaluate ("SetRGB(" & AC.Address(False, False) & ", " & RGB & ")")
''Syntac for call mus tbe perfect including spaces
Sub SetRGB(Target As Range, R As Integer, G As Integer, B As Integer)
Target.Interior.Color = RGB(R, G, B)
End Sub
Sub SetRGB_Clear(Target As Range)
''Clears warning
''https://docs.microsoft.com/en-us/office/vba/api/excel.xlpattern
''https://docs.microsoft.com/en-us/office/vba/api/excel.xlcolorindex
With Target.Interior
.Pattern = xlPatternNone
.PatternColorIndex = xlColorIndexNone ' xlAutomatic
.ColorIndex = xlColorIndexNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Comments
Post a Comment