16

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!

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
AJP
  • 26,547
  • 23
  • 88
  • 127

3 Answers3

39

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

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    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](http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.interior.colorindex%28v=office.11%29.aspx) doesn't say anything about transparency. [This page lists x1None](http://msdn.microsoft.com/en-us/library/microsoft.office.core.xlconstants.aspx) 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! – AJP Feb 02 '12 at 18:33
  • 3
    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. – Tim Williams Feb 02 '12 at 21:07
  • 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` – AJP Feb 04 '12 at 17:47
0
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.ColorIndex = 8
        .EntireColumn.Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True
End Sub
rayryeng
  • 102,964
  • 22
  • 184
  • 193
safw
  • 11
0

Perhaps a simple approach would be (Symbol).(line or background)Color = -1 'Transparent.

Baum mit Augen
  • 49,044
  • 25
  • 144
  • 182
Tom
  • 1