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

Use RGB to color a font in cell

This company is rebradning all their workbooks, but the colors are all over the place so cannot use the color index to be sure that a field is a particular color.  I need to be able to set the color of the font for cell N5 in each workbook a dark blue or grey - anything that is dark and readable, but I can't seem to get the RGB to work.
For Each wks In Worksheets
        If Not wks.Name = "Table_of_Contents" And Not wks.Name = "DataSource" Then
                wks.Activate
                wks.Range("N5").Select
                wks.Range("N5").Font.Color = RGB(0, 0, 0)
                wks.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                    "Table_of_Contents!A1", TextToDisplay:="TOC"
            End If
    Next

Open in new window

0
ssmith94015
Asked:
ssmith94015
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
jppintoCommented:
Not sure what is the question here but do you know this articles about colors in Excel?

http://www.mvps.org/dmcritchie/excel/colors.htm
http://www.cpearson.com/excel/colors.aspx

Hope that they can help you.

jppinto
0
 
ssmith94015Author Commented:
Sorry I was not clear.  I want to use the RGB function to be sure the color of the font in cell N5 on all worksheet is, say, dark blue.  But the code does not change the font color, it is still the sick green, which is in the color index.
0
 
jppintoCommented:
Did you tryed like this?

jppinto
For Each wks In Worksheets
    If wks.Name <> "Table_of_Contents" And wks.Name <> "DataSource" Then
        wks.Activate
        wks.Range("N5").Select
        wks.Range("N5").Font.Color = RGB(0, 0, 0)
        wks.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
            "Table_of_Contents!A1", TextToDisplay:="TOC"
    End If
Next

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
byundtCommented:
Which version of Excel are you using? It is much easier to change a color in Excel 2007 and later than in Excel 2003.

To get the desired RGB color, I suggest the following procedure (Excel 2007 and later):
1) In a blank cell, open the Home...Fill menu item and choose More Colors from the resulting dialog
2) On the Custom tab of the next dialog, choose the desired gray on the right-hand side. Copy down the RGB settings that you see. The one I picked was RGB(117,117,117)
0
 
byundtCommented:
In Excel 2003 and earlier, try the following:
1) Open the Tools...Options menu item and go to the Color tab
2) Click the Reset button and see if that fixes your problem
3) If not, then click one of the color patches and click the Modify button.
4) On the Custom tab of the resulting dialog, choose the desired color.
5) Once again, make note of the RGB setting. You will also see that the color patch has changed its color. If you apply that new color as the fill for a cell, you can use the new color in VBA by referring to its ColorIndex:
MsgBox ActiveCell.Interior.ColorIndex
0
 
balatheexpertCommented:
hi,

This is because, the color for hyperlink is same across workbook and you cant set a font color for an hyperlink.

if you are using 2003 or prior versions, you can set the color for hyperlink in the color pallette uner
Tools->Options. I think it should be 6th or 7th color.

so, instead of trying to change the hyperlink's color, change the background so that the text is visible properly!!!!

Thanks,
Bala
0
 
ssmith94015Author Commented:
Sorry it took so long to get back but been really busy.  Yes, the easies was to just change the hyperlink as mentioned and that worked the best.  I split as it took me such a whiel to get back that I would like to give credit to the second person that gave me bascially the same advice.  I did try VBA coding, but it just balked, but the hyperlink modification worked.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now