时间戳

首页 > 解决方案 > How do I assign an Interior-Object to a Range?

vba - How do I assign an Interior-Object to a Range?

问题描述

I have created an Interior variable in vba to store the style of a certain cell. Excel lets me do it but when I try to apply that style to any other cell it does not work.

Sub JustTest()
    Dim X As Interior
    Set X = Range("H12").Interior   'stores the Interior values into X

    X.TintAndShade = -0.05          'could alter the values if needed to

    Set Range("H15").Interior = X   'this line does not work

End Sub

I was expecting that I could "copy" a style from (in this example) Range H12 to Range H15 but I get a runtime error 438. Doing it this way I could alter the Interior values at runtime. I guess it's simple but I am a blockhead. Anyone an idea? Thanks in advance!

标签： vba

解决方案

You could use a sub to set all the (non-object) properties of interior thus achieving what you want. Instead of your Set Range("H15").Interior = X statement you would use Call setInterior(Range("H15"),X).

Sub setInterior(rg As Range, rgInt As interior)
    Dim rangeInt As interior

    Set rangeInt = rg.interior

    On Error Resume Next

    With rangeInt 
'        .Application
        .Color = rgInt.Color
        .ColorIndex = rgInt.ColorIndex
'        .Creator
'        .Gradient
         .InvertIfNegative = rgInt.InvertIfNegative
'        .Parent
        .Pattern = rgInt.Pattern
        .PatternColor = rgInt.PatternColor
        .PatternColorIndex = rgInt.PatternColorIndex
        .PatternThemeColor = rgInt.PatternThemeColor
        .PatternTintAndShade = rgInt.PatternTintAndShade
        .ThemeColor = rgInt.ThemeColor
        .TintAndShade = rgInt.TintAndShade
    End With

End Sub

Object/Read only properties are commented but shown for completeness. InvertIfNegative seems to sometimes have an error, thus the On Error Resume Next statement to avoid problems with it.

推荐阅读