• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3065
  • Last Modified:

How convert .Color property number to RBG values?

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
NiklasMoller
Asked:
NiklasMoller
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
karthikajhancyCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@karthikajhancy,

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

I did.
0
 
CluskittCommented:
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
 
NiklasMollerAuthor Commented:
thank you both for helping me, since teylyn was first, I will award points to him/her
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now