Solved

How convert .Color property number to RBG values?

Posted on 2010-09-02
5
2,749 Views
Last Modified: 2012-05-10
I want to convert the number value of a cell background to RGB values in vba

pseudo code:
So i get a color from a certain cell...
myColor = myCell.Interior.Color    'will give me a Long value like 65335
'convert that value to the equivalent values in rgb:
redValue = convertToRGB("red", 65335)
greenValue = convertToRGB("green", 65335)
blueValue = convertToRGB("blue", 65335)

then I can color the next cell using:
myOtherCell.Interior.Color =  RBG(redValue, greenValue, blueValue)

Yes, i know that I can use colorIndex, and that I could equate the two color objects just to copy the color from one cell to another, however, that is not the point. I need this for other purposes, so I really need to be able to translate the values.

My question: how would the above example  "convertToRGB() function work??
It doesnt have to work like the example above, the important thing is that I want to pass in a Long number representing an RGB value, and instead get the RGB values back.

thanks!

0
Comment
Question by:NiklasMoller
5 Comments
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
Hello NiklasMoller,

see Chip Pearson's page, http://www.cpearson.com/excel/colors.aspx -- scoll down to

Functions For Color Values

The modColorFunctions module contains a number of functions for working with RGB colors and color index values.

ColorIndexOfRGBLong
This returns the Color Index value of the specified RGB Long color value, if it exists in the current palette. Otherwise, it returns 0.

IsColorpaletteDefault
This returns True if the palette associated with the specified workbook is the application default palette. This returns False if the palette has been modified with Workbook.Colors.

IsColorIndexDefault
This returns True if the color associated with the specified color index is the same as the application default color index value. This tells you if the color associated with a color index value has been changed.

RGBComponentsFromRGBLongToVariables
This splits an RGB Long value into the constituent red, green, and blue values, which are returned to the caller in the ByRef variables. The function's result is True if the input value was a valid RGB color or False if the input value was not a valid RGB color. For example,

    Dim RGBColor As Long
    Dim Red As Long
    Dim Green As Long
    Dim Blue As Long
    Dim B As Boolean
   
    RGBColor = ActiveCell.Interior.Color
    B = RGBComponentsFromRGBLongToVariables(RGBColor, Red, Green, Blue)
    If B = True Then
        Debug.Print "Red: " & Red, "Green: " & Green, "Blue: " & Blue
    Else
        Debug.Print "Invalid value in RGBColor"
    End If

RGBComponentsFromRGBLong
This splits an RGB Long color value into the red, green, and blue components and returns them as an array of Longs.

    Arr(1) = Red
    Arr(2) = Green
    Arr(3) = Blue
0
 

Expert Comment

by:karthikajhancy
Comment Utility
IsColorpaletteDefault
This returns True if the palette associated with the specified workbook is the application default palette. This returns False if the palette has been modified with Workbook.Colors.

IsColorIndexDefault
This returns True if the color associated with the specified color index is the same as the application default color index value. This tells you if the color associated with a color index value has been changed.

RGBComponentsFromRGBLongToVariables
This splits an RGB Long value into the constituent red, green, and blue values, which are returned to the caller in the ByRef variables. The function's result is True if the input value was a valid RGB color or False if the input value was not a valid RGB color. For example,

    Dim RGBColor As Long
    Dim Red As Long
    Dim Green As Long
    Dim Blue As Long
    Dim B As Boolean
   
    RGBColor = ActiveCell.Interior.Color
    B = RGBComponentsFromRGBLongToVariables(RGBColor, Red, Green, Blue)
    If B = True Then
        Debug.Print "Red: " & Red, "Green: " & Green, "Blue: " & Blue
    Else
        Debug.Print "Invalid value in RGBColor"
    End If

RGBComponentsFromRGBLong
This splits an RGB Long color value into the red, green, and blue components and returns them as an array of Longs.

    Arr(1) = Red
    Arr(2) = Green
    Arr(3) = Blue
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
@karthikajhancy,

if you provide a solution that you have not come up with yourself, please provide the source.

I did.
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Easiest solution is to convert the number to hex, (for example, FF00FF) then get the values for each and convert back to decimal. In this case, it would be R: FF (255), G: 00 (0), B: FF (255).

Something along the lines of:
VarR=Right(Hex(MyColor),2)    'hex would return BGR
VarG=Mid(Hex(MyColor),3,2)
VarB=Left(Hex(MyColor),2)
0
 
LVL 5

Author Closing Comment

by:NiklasMoller
Comment Utility
thank you both for helping me, since teylyn was first, I will award points to him/her
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
identifying alphanumerics in a column 6 18
Excel VBA - format a Shape same as a Cell 7 22
Macro 3 13
Macro to delete column 3 0
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now