Copy colour

[VegasNath]

Me again......

I am using a select statement to add a fill colour to cells based upon the cell value. However, I am trying to avoid explicit specification of the fill colour.

Code: Select all

            Select Case Rng.Value
                Case "1132"
                    Rng.Offset(0, 23).Interior.Color = 'Copy fill from I12
                Case "1137"
                    Rng.Offset(0, 23).Interior.Color = 'Copy fill from I13
                Case "1138"
                    Rng.Offset(0, 23).Interior.Color = 'Copy fill from I14
                Case "1140"
                    Rng.Offset(0, 23).Interior.Color = 'Copy fill from I15
                Case "1141"
                    Rng.Offset(0, 23).Interior.Color = 'Copy fill from I16
                End Select

How can I use the fill colour from one cell to fill another?

[HansV]

Rng.Offset(0, 23).Interior.Color = Range("I12").Interior.Color

etc.

[VegasNath]

I should have known that!

May I ask another...

Code: Select all

    With Selection.Interior
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
    End With

Is a with statement actually required here or is there an equivalent single statement? If not, how can this be used within an If statement?

[HansV]

Do you really need TintAndShade here? This property is used to make the color darker or lighter. Its value can vary from -1 (darkest) to +1 (brightest), with 0 being neutral. In most situations, you only need to specify a color.

[VegasNath]

I have just taken the colour from the palette as an example, but all of them on the standard palette seem to use this new 'style'? Fill colours were much easier in xl2003.

[HansV]

Excel 2003 limited you to 56 colours per workbook. Excel 2007 and later offer unlimited colours, but you can still use the old ColorIndex or Color properties if you wish, you aren't obliged to use ThemeColor and TintAndShade.

[VegasNath]

Great, Thanks.