Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: Excel VBA cell format

    Hi all,

    I have a process that breaks down and exports particular data from a access query to excel, and i would like to format particular cells in trems of font and cell color.

    I have the code to do this but i do not know the codes for the color ie the "ColorIndex"....

    Excel.Cells(3, 3).Interior.ColorIndex = 12

    does anybody know where i can find the list of available colors and their respective numbers????

    i am after a kinda grey/gunmetal color...

    many thanks.

    Jnr.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: Excel VBA cell format

    Hi

    if you just press F1 after you type colorindex in Excel VBA Window then you can get the listings of the colorindex numbers from the help file. For Grey i tend to use a colorindex of 15.

    HTH

    David

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    This code will dispay the color codes

    Code:
    Sub DisplayColorPalette()
        Dim r As Long
        Worksheets.add
        Range("A1") = "Index"
        Range("B1") = "Color"
        Range("C1") = "Value"
        Range("D1") = "Red"
        Range("E1") = "Green"
        Range("F1") = "Blue"
        For r = 2 To 57
            Cells(r, 1) = r - 1
            Cells(r, 2).Interior.Color = ActiveWorkbook.Colors(r - 1)
            Cells(r, 3) = ActiveWorkbook.Colors(r - 1)
            Cells(r, 4) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 0 And 255
            Cells(r, 5) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 1 And 255
            Cells(r, 6) = ActiveWorkbook.Colors(r - 1) \ 256 ^ 2 And 255
        Next r
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Oct 2003
    Posts
    58

    Re: Excel VBA cell format

    Thanks all,

    Next question is how can i set the width and height to auto fill?

    I have large string values that i want to fill in each cell but they seem to always get cut short...

    also....

    I have managed to save the sheet in html format (using the ActiveWorkbook.SaveAs function) if i put html code into the cell value can i write tags to the page?

    thankyou,

    Jnr.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •