Solved

How convert .Color property number to RBG values?

Posted on 2010-09-02
5
2,836 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

838 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