How to use VBA to make a cell in Excel 2007 transparent

Question

I currently have:

Range("Z1").Interior.Color = RGB(255, 255, 255)

But this wipes out the borders of the cells. Instead I'd just like to set the transparency of the cells in range to 1.0. The docs seem to suggest it doesn't exist (?).

Thanks!

Solution

Range("Z1").Interior.ColorIndex = xlNone

Comments

[AJP]

Perfect. Thank you so much. Could you point me to the reference of this knowledge please? MSDN didn't seem to have it immediately accessible: ColorIndex doesn't say anything about transparency. This page lists x1None but with no description (?! grr) Interestingly it also lists xlTransparent which has a different value that (unsurprisingly) doesn't work. Thanks so much for your help anyway!

[Tim Williams]

Using xlNone is the same as selecting "No fill" from the menu in Excel: it's not really "transparent". Technically it probably should be xlColorIndexNone but they have the same value... The ColorIndex property applies to other object such as Font and Border, so that's why you may be seeing values which have no effect when applied to Interior.ColorIndex For reference sometimes the best thing to do is just record a macro and see what gets generated. The object browser (press F2 in the VB Editor) is also useful.

[AJP]

Again, Perfect, thanks Tim, wish I could give this more points. Recording a macro worked brilliantly: Sub Macro1() Range("T1:W4").Select With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub

[rayryeng]

Some explanation would be nice.