I am just starting with trying to edit cells in an excel worksheet and through this forum have managed to take the first steps. I need to be able to change the font and background colors of individual cells. I can cahnge the font color with the colorindex property but I need to be able to do it with RGB color references.
Here’s what I’ve got so far:
Sub Click(Source As Button)
Dim xlApp As Variant
Dim xlsheet As Variant
Set xlApp = CreateObject("Excel.application")
xlApp.Visible = True
Set xlwb=xlapp.workbooks.add("c:\book1.xlt")
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
XlApp.Windows(1).DisplayGridlines=False
xlsheet.Name = "TEST"
xlsheet.activate
xlsheet.range("A4").value="Test Test"
xlsheet.range("A4").cells.font.name="verdana"
xlsheet.range("A4").cells.font.bold="true"
xlsheet.range("A4").cells.font.colorindex="9"
'I need to be able to do this with rgbreferences
Set xlApp = Nothing
end sub
Can anyone point me in the right direction toward resources?
You can’t do more than Excel does – the colour indices are the only colours available to Excel by its own methods. You could create a List as a lookup “table” to translate RBG to colour index, but that list would be limited to the values allowed by Excel:
Dim GetIndex List As Integer
GetIndex(“0,0,0”) = 1
and so forth. You’d also need to code a “get closest” function to translate your RGB value into a valid RGB value for excel so that only valid values are used to pull the colour index from the list.
Thanks for the reply. I think I must have been confused by the MS Script Editor Help on the color property:
“When you set this property, you can use either a Long value representing a red-green-blue color value or a String value naming a valid HTML color value. For example, to set the object color to red, you could use the hexadecimal value &HFF, the decimal value 255, or the string value “red.” In Microsoft Visual Basic, you can use the RGB function to create a red-green-blue color value (red is RGB(255,0,0)).”
It mentions that the above applies to the font object…
