Results 1 to 5 of 5

VBA to set a fill effect in a cell

This is a discussion on VBA to set a fill effect in a cell within the Excel Questions forums, part of the Question Forums category; Hi Folks, Here's a problem I'd like to be able to solve in VBA : Retrieve the current cell color ...

  1. #1
    New Member
    Join Date
    Aug 2006
    Location
    London, England
    Posts
    49

    Question VBA to set a fill effect in a cell

    Hi Folks,

    Here's a problem I'd like to be able to solve in VBA:


    Retrieve the current cell color (let's say A1 = 'green')

    Format A1's cell colors (using the VBA equivalent of the 'Format Cells' / 'Fill' / 'Fill Effects' / '2 color Gradient' feature) so that the background cell color (color 2) is set to 'green' with color 1 being set to 'white' with a shading style of "From Center"


    I've searched for pointers on how to do this without success - is it even possible to do what I'm trying to achieve?

    Any hints / tips / answers much appreciated

    (If it helps at all I'm using Excel 2010 but any solution would need to be backward compatible with Excel 2007.)

    Thx

    shawthingz

  2. #2
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,641

    Default Re: VBA to set a fill effect in a cell

    Did you use the macro recorder already?
    It will give you code that you can work on.
    Regards,


    Wigi


    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  3. #3
    New Member
    Join Date
    Aug 2006
    Location
    London, England
    Posts
    49

    Talking Re: VBA to set a fill effect in a cell

    Thanks wigi!

    I now feel a bit of a "Muppet" for posting this question on the board as I should have (obviously) tried this approach first, as it's "the simplest thing that could possibly work".

    However, after a short spell of feeling extremely embarassed, here's the code (courtesy of the Macro recorder with a small tweak from me) to put a nice white gradient highlight "cross" into a the A1 cell without changing it's original colour:

    CurrentCellColour = Range("A1").Interior.Color

    Range("A1").Select
    With Selection.Interior
    .Pattern = xlPatternRectangularGradient
    .Gradient.RectangleLeft = 0.5
    .Gradient.RectangleRight = 0.5
    .Gradient.RectangleTop = 0.5
    .Gradient.RectangleBottom = 0.5
    .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
    .Color = CurrentCellColour
    .TintAndShade = 0
    End With

    Hope this helps others out there in the future!

  4. #4
    Board Regular
    Join Date
    Jul 2006
    Location
    Brussels
    Posts
    7,641

    Default Re: VBA to set a fill effect in a cell

    If you write the With statements in a normal and clear way, you get:

    Code:
    Sub Wigi()
        With Selection.Interior
            l = .Color
            .Pattern = xlPatternRectangularGradient
            With .Gradient
                .RectangleLeft = 0.5
                .RectangleRight = 0.5
                .RectangleTop = 0.5
                .RectangleBottom = 0.5
                With .ColorStops
                    .Clear
                    .Add(0).ThemeColor = xlThemeColorDark1
                    .Add(1).Color = l
                End With
            End With
        End With
    End Sub
    Regards,


    Wigi


    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  5. #5
    New Member
    Join Date
    Aug 2006
    Location
    London, England
    Posts
    49

    Default Re: VBA to set a fill effect in a cell

    Wigi,

    Many thanks for another swift response on this thread & providing a much cleaner / elegant solution to me than the macro recorder did - as I'm a self-taught VBA 'hacker' I'm continually learning & really appreciate all the help that's available through these forums!

    shawthingz

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com