We help IT Professionals succeed at work.

RGB value of selected shaded cell

Andreas Hermle
on
480 Views
Last Modified: 2012-05-06
Dear Experts:

I would like to retrieve the RGB value of the selected shaded cell of a word table and display it in a msgbox.
Help is much appreciated. Thank you very much in advance. Regards, Andreas
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
Hello AndreasHermle,

SOmething like the following perhaps:

Regards,
Chris
Sub cellRGB()
Dim str As String 
str = "Color is :" & vbCrLf & vbCrLf
With Application.ActiveCell.Interior
    str = str & "Red:   " & CStr(&HFF& And .Color) & vbCrLf
    str = str & "Green: " & CStr((&HFF00& And .Color) \ 256) & vbCrLf
    str = str & "Blue:  " & CStr(.Color \ 65536) & vbCrLf
End With
MsgBox str, vbOKOnly, "Cell Interior RGB" 
End Sub

Open in new window

GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2011

Commented:
Keeping the structure from Grahams solution and merging it with the RGB display produces for example the following:

Chris
Function RGBValue(rng As Range) As String
    Dim cl As Word.Cell
    Dim RGBValueF As Long
    Dim RGBValueB As Long
    Set cl = rng.Cells(1)
    RGBValueF = cl.Shading.ForegroundPatternColor
    RGBValueB = cl.Shading.BackgroundPatternColor
    RGBValue = "Foreground; " & RGB(RGBValueF) & vbCrLf & "Background; " & RGB(RGBValueB)
End Function
 
Function RGB(lng As Long) As String
    lng = Abs(lng)
    RGB = "Color is :" & vbCrLf & vbCrLf
    RGB = RGB & "Red:   " & CStr(&HFF& And lng) & vbCrLf
    RGB = RGB & "Green: " & CStr((&HFF00& And lng) \ 256) & vbCrLf
    RGB = RGB & "Blue:  " & CStr(lng \ 65536) & vbCrLf
End Function

Open in new window

Andreas HermleTeam leader

Author

Commented:
Dear Graham and Chris,

thank you very much for your swift help. I do not know how to call up this function. I guess I need to call it from an existing macro. If the latter is true, this macro should be just a stand-alone macro with no more code.
CERTIFIED EXPERT
Top Expert 2011

Commented:
How would you like to call it? ... for example run via the tools macro run menu using the activecell?

Chris
GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
Other options are to attach the macro to a new button on a toolbar and/or to create a keystroke shortcut. These are both done via the Customise dialogue.
Andreas HermleTeam leader

Author

Commented:
Dear Chris and Graham,
I guess you did not understand me correctly. Am I right, but this function must be 'embedded' in a macro starting with Sub someName() and End Sub? If running this macro (the cursor resides in some selected cell of some table) a msgbox should come up, telling me which RGB value the selected cell has.

I hope I could make myself clear.

Regards, Andreas
CERTIFIED EXPERT
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Andreas HermleTeam leader

Author

Commented:
Dear Chris,
that's it. Thank you very much for your professional help. I am not quite sure about how to distribute the points. Graham also contributed to the solution, at least at the beginning. Would it be ok to do a 350 to 150 split or 400 to 100 with you getting the bigger share?

Regards, Andreas  
CERTIFIED EXPERT
Top Expert 2011

Commented:
Absolutely right that Graham should get a share so whichever split you like ...

But hes a genius and i'm a mere master so perhaps 499 to me and 1 to him ;o) nah joshing aside I would say 350 : 150 is the fairer of the options you proposed.

Chris
Andreas HermleTeam leader

Author

Commented:
Again, thank you for your professional help. Regards, Andreas
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.