[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How convert .Color property number to RBG values?

Posted on 2010-09-02
5
Medium Priority
?
3,004 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 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
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

656 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