Determining the RGB Value of a Color

Hello guys .. I have a question : how to convert HSL values to RGB values in Excel using VBA?
if you have any idea or code please help me..
thanks for everything

Ibnu

Add the first line above the 2nd

Application.Volatile
C = rcell.Interior.Color

how to make that funcion auto run when the valeu cell change?

First let me complement you for a very useful website.

Does this also work for the font color? If so, I am having difficulty applying these techniques to font colors. For example, the Custom Colors suggestion when applied to fonts does not produce any result for me in Excel 2016. Just 0s for the RGB values regardless of the color of the font. Modifying the macros to refer to Font in lieu of Interior behaves similarly. I want to develop some vba code that would place values in an adjacent cell that is triggered by the font color of the trigger cell. Specifically, for a list of stocks with colored fonts (vbBlue for Bull and vbRed for Bear), I want the trigger to be the Blue or Red font color. If it does apply, any suggestions as to how to make it work would be much appreciated. Thank you in advance for any help you can provide.

If you want to make one cell similar to another, format painter may help, unless you want only part of the format the same.

I do have conditional formatting applied to some of the cells (conditional formatting - duplicates).

How do I find those formatted cells? In other words, I want the background color of the cells so I can use Find on them.

Never mind - I found it (pun included). The Find command's "Format" has the option to pick format from cell. Good. Now I don't need the RGB!

I converted the xls file to xlsm so I could use your first macro at the top of this page. Why is it that as soon as I create the macro, even though in Advanced Options I ensured the box alongside, "show formulas in cells instead of their calculated results" is unclicked - all formulas are now shown as formulas, not the calculated results? This is true even after I delete the macro. The only way to undo it is by using a previous version of the file.

Thanks - really useful to me.

Thanks so much, getRGB1 was very very helpful.

Gusti

Thanks so much for the tip. It was very helpful.

Thank you, getRGB2 has been really useful to me.

Thanks a lot.
J

Hi, thank you for the getRGB1 function. It's exactly what I was looking for. First I a similar function on another site but it did not return the correct value. Your function, however, works perfectly :-)
Thank again. Frederick

@ Willy

You are absolutely right, but hard-coding each assignment, cell-by-cell, would be more code and more maintenance over the development and operational life of the project, right?

I use a lot of what I call "formatting by exemplar" (using the font/colors/borders etc from one cell and applying it to others) so I converted my standard Sub by making the parameters into variables, adding the Set assignments, and removing all of the other formatting that can be passed from one cell to another.

By using the exemplar formatting approach I can have users, using Excel, show me exactly what they want to see as far as format is concerned. I can give them an first cut layout. from that point many of them can work through the process. With others, where I have to drive the mouse, it eliminates a lot of note-taking in meetings as well as "Could you make it light/darker greener/bluer then get back with me?" discussions.

On the back end, give those cells meaningful Names, e.g. on one project I have "fmtReleaseBanner" and "fmtNotesHeader" then I can pass the named range and the target range to one subroutine that performs the same action, whether it is in the NewMonth, UpdateForMeeting, or PublishResults main Sub.

Just my opinion!

Here is a much shorter version of the getRGB3 function in this tip:

Function getRGB3(rcell As Range, Optional opt As Integer) As Long
Dim C As Long
C = rcell.Interior.Color
Select Case opt
Case 1: getRGB3 = C Mod 256
Case 2: getRGB3 = C 256 Mod 256
Case 3: getRGB3 = C 65536 Mod 256
Case Else: getRGB3 = C
End Select
End Function

@ Bigger Don

If you put the macro in the sheet's code page you can reduce it to one line:

Sub copycolor()
Range("A3").Interior.Color = Range("D3").Interior.Color
End Sub

@ James

Why worry about the Hex value unless you want to manipulate it before assigning it to another cell? You can simply assign one cell's Interior.Color directly to another.

Sub copycolor()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("Sheet1").Range("D3")
Set rng2 = Worksheets("Sheet1").Range("A3")
rng2.Interior.Color = rng1.Interior.Color

End Sub

I don't want to get the RGB, I want to set the RGB. Read the sColor hex from a cell and set that as the background colour of the cell "Asking".

Cheers Jim