Solved

How convert .Color property number to RBG values?

Posted on 2010-09-02
5
2,804 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:
Ingeborg Hawighorst earned 500 total points
ID: 33585367
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
ID: 33585375
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:Ingeborg Hawighorst
ID: 33585385
@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
ID: 33585419
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
ID: 33585423
thank you both for helping me, since teylyn was first, I will award points to him/her
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

805 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