VBA to set a fill effect in a cell

shawthingz

New Member
Joined
Aug 19, 2006
Messages
49
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Did you use the macro recorder already?
It will give you code that you can work on.
 
Upvote 0
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! :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top