Excel VBA Macro to change cell color

[MaggieSmaggie]

I want to create a Macro and assign it to a button. This is what I want to happen:


I select a cell that I want to be changed by the macro to have a certain color.
When I click the macro button, the cell I had selected changes colors.


I know you can do this by changing the background color of a cell. However, I would like to use this Macro to be able to quickly change a cell to different fills or patterns by just clicking the button.


So far, when I write the code it can only work for an assigned range, like F9. How to I make it work for the active cell instead of always this cell?

Sub OrganicCover()
'
' OrganicCover Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("F9").Select
With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 90
.Gradient.ColorStops.Clear
End With
With Selection.Interior.Gradient.ColorStops.Add(0)
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.400006103701895
End With
With Selection.Interior.Gradient.ColorStops.Add(1)
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.400006103701895
End With
Range("F11").Select
End Sub

 

[Joe4]

Welcome to the Board!

Just remove the literal cell selection from your code, and replace all references of Selection to ActiveCell:

Sub OrganicCover()
'
' OrganicCover Macro
'
' Keyboard Shortcut: Ctrl+q
'
    With ActiveCell.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 90
        .Gradient.ColorStops.Clear
    End With
    
    With ActiveCell.Interior.Gradient.ColorStops.Add(0)
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.400006103701895
    End With
    
    With ActiveCell.Interior.Gradient.ColorStops.Add(1)
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.400006103701895
    End With


End Sub

Also note, using Code Tags makes your code much more readable (https://www.mrexcel.com/forum/board...post-your-visual-basic-applications-code.html)

 

[MaggieSmaggie]

Thank you!!!

 

[Joe4]

You are welcome!