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
Solved

RGB value of selected shaded cell

Posted on 2009-05-03
11
455 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
0
Comment
Question by:AndreasHermle
  • 5
  • 4
  • 2
11 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24291236
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

0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 150 total points
ID: 24291512
Hello Andreas,

Chris has done a good job on splitting the colour components, but I think that he has confused Word and Excel.

This code reports the numeric values for the foreground and background values for the selected cells. You need to provide a range that includes a cell. I wopuld suggest something like:


MsgBox RGBValue(selection.range)
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; " & RGBValueF & vbCrLf & "Background; " & RGBValueB
End Function

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24293094
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

0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:AndreasHermle
ID: 24303249
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.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24303287
How would you like to call it? ... for example run via the tools macro run menu using the activecell?

Chris
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24303707
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.
0
 

Author Comment

by:AndreasHermle
ID: 24313127
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
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 350 total points
ID: 24313212
Okay then try the following change.

Put the cursor in a cell and press alt + F8 then select RGBValue

Chris
Sub RGBValue()
    Dim cl As Word.Cell
    Dim RGBValueF As Long
    Dim RGBValueB As Long
    If Not Selection.Information(wdWithInTable) Then Exit Sub
    Set cl = Application.Selection.Range.Cells(1)
    RGBValueF = cl.Shading.ForegroundPatternColor
    RGBValueB = cl.Shading.BackgroundPatternColor
    MsgBox "Foreground; " & RGB(RGBValueF) & vbCrLf & "Background; " & RGB(RGBValueB), vbOKOnly, "Activecell Color VAlues"
End Sub
 
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

0
 

Author Comment

by:AndreasHermle
ID: 24319233
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  
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 24319288
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
0
 

Author Closing Comment

by:AndreasHermle
ID: 31577373
Again, thank you for your professional help. Regards, Andreas
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Do you ever need to create a 20 page Word document for some testing purpose? Are you tired of copying & pasting old boring "lorem ipsum" text over and over again, increasing font size and line space in order to make the document 20+ pages long? Look…
It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

840 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