Excel VBA cell format

130213 · #1 (**permalink**) 03-25-04, 04:59

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.

DavidCoutts · #2 (**permalink**) 03-25-04, 05:29

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

shades · #3 (**permalink**) 03-25-04, 09:35

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

130213 · #4 (**permalink**) 03-25-04, 12:01

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech