Written by Allen Wyatt (last updated December 30, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Bassel needs a formula that can convert HSL color values (hue, saturation, and lightness values) to RGB color values (red, blue, and green values). He wonders if anyone can point him to either a worksheet formula that will do this or a way to do it in a macro.
Converting from HSL to RGB values is not a particularly trivial undertaking. For those interested in the math behind it, you can check out the Wikipedia article on the topic.
If you need to do the conversion infrequently, the easiest way is to follow these steps:
Figure 1. The Custom tab of the Colors dialog box.
This approach assumes that each of your HSL values can be express in the range of 0 to 255. If, however, your HSL values are either an angle (for hue) or a percentage (for saturation and luminance), then you'll need to convert them manually before entering them in step 6. You can convert an angle value by multiplying the angle by 255 and then dividing by 360. Percentages can be converted by multiplying them by 2.55.
If you have a need to get the values more often, then creating your own user-defined function to do the conversion will help. The following example accepts three parameters (Hue, Saturation, and Luminance) and returns a string containing the decimal RGB values separated by commas.
Function HSLtoRGB(Hue As Integer, Saturation As Integer, _ Luminance As Integer) As String Dim r As Integer Dim g As Integer Dim b As Integer Dim C As Double Dim X As Double Dim m As Double Dim rfrac As Double Dim gfrac As Double Dim bfrac As Double Dim hangle As Double Dim hfrac As Double Dim sfrac As Double Dim lfrac As Double If (Saturation = 0) Then r = 255 g = 255 b = 255 Else lfrac = Luminance / 255 hangle = Hue / 255 * 360 sfrac = Saturation / 255 C = (1 - Abs(2 * lfrac - 1)) * sfrac hfrac = hangle / 60 hfrac = hfrac - Int(hfrac / 2) * 2 'fmod calc X = (1 - Abs(hfrac - 1)) * C m = lfrac - C / 2 Select Case hangle Case Is < 60 rfrac = C gfrac = X bfrac = 0 Case Is < 120 rfrac = X gfrac = C bfrac = 0 Case Is < 180 rfrac = 0 gfrac = C bfrac = X Case Is < 240 rfrac = 0 gfrac = X bfrac = C Case Is < 300 rfrac = X gfrac = 0 bfrac = C Case Else rfrac = C gfrac = 0 bfrac = X End Select r = Round((rfrac + m) * 255) g = Round((gfrac + m) * 255) b = Round((bfrac + m) * 255) End If HSLtoRGB = Str(r) & "," & Str(g) & "," & Str(b) End Function
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13535) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When creating a workbook that will be used by others, you may wish to ensure that the user fills in some cells before ...
Discover MoreIf you have a macro that selects different columns in a worksheet while processing information, you may get some ...
Discover MoreNeed to know the address of the cell that is currently selected? The function and macro highlighted in this tip will come ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-08-13 21:33:39
Beta
Great code! One error for me: I had to change all Integer to Long. I used this in a file that has many macros and uses a lot of memory. No clue why Integer didn't work for me.
2023-12-31 17:06:11
George Reasoner
There is an error in the macro. If Saturation is 0, then the R, G and B values are all equal to the Luminance value.
Function HSLtoRGB(Hue As Integer, Saturation As Integer, _
Luminance As Integer) As String
Dim r As Integer
Dim g As Integer
Dim b As Integer
Dim C As Double
Dim X As Double
Dim m As Double
Dim rfrac As Double
Dim gfrac As Double
Dim bfrac As Double
Dim hangle As Double
Dim hfrac As Double
Dim sfrac As Double
Dim lfrac As Double
If (Saturation = 0) Then
r = Luminance
g = Luminance
b = Luminance
Else
lfrac = Luminance / 255
hangle = Hue / 255 * 360
sfrac = Saturation / 255
C = (1 - Abs(2 * lfrac - 1)) * sfrac
hfrac = hangle / 60
hfrac = hfrac - Int(hfrac / 2) * 2 'fmod calc
X = (1 - Abs(hfrac - 1)) * C
m = lfrac - C / 2
Select Case hangle
Case Is < 60
rfrac = C
gfrac = X
bfrac = 0
Case Is < 120
rfrac = X
gfrac = C
bfrac = 0
Case Is < 180
rfrac = 0
gfrac = C
bfrac = X
Case Is < 240
rfrac = 0
gfrac = X
bfrac = C
Case Is < 300
rfrac = X
gfrac = 0
bfrac = C
Case Else
rfrac = C
gfrac = 0
bfrac = X
End Select
r = Round((rfrac + m) * 255)
g = Round((gfrac + m) * 255)
b = Round((bfrac + m) * 255)
End If
HSLtoRGB = Str(r) & "," & Str(g) & "," & Str(b)
End Function
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments