Run macro in different cell than recorded

[Moomoo327]

Hello All,

I have a worksheet that has multiple options for us to highlight. I have recorded a macro to change the fill to black, bold the font and change it white. How Do i now change this code to work on any of the cells (all merged) I please.

Sub Black_Fill()
'
' Black_Fill Macro
'

'
    Range("A10:G10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End Sub

I will end up making a macro to revert back to original format once the option i need highlighted on my worksheet is no longer needed.

I would like to in the end add a cmd button to my context menu in the end.

Appreciate the assistance from you all!

-A

[TMS]

Take this line out.

Range("A10:G10").Select

Then it will work with whatever cells are selected when you run the macro.

[TMS]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Roel Jongman]

Next to the suggestion of TMS so that you can apply your formatting to any range you select yourself.
You should also look at the code for settings that are recorded but do not need changing.

I shortend your macro by only leaving in the most essential options you changed.
Recording a macro is a good way to record what you want but it also gives a lot of redundant code
removing things you do not need to change helps prevent unexpected result and it also helps you to understand the code

Sub Black_Fill()
'
' Black_Fill Macro
'

'
' set the backgroundcolor of the selected cells
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
    End With
' set the formatting of the text in the selection
    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .ThemeColor = xlThemeColorDark1
    End With
'Set the alignments of the selected cells
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .MergeCells = True
    End With
End Sub

[Moomoo327]

Thats awesome! Thank you for the comments in there as well.

 With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
      End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .ThemeColor = xlThemeColorLight1
    End With
End Sub

Is that code up there simplified from below?

With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
        End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End Sub

I am trying to add a Black_Fill and Clear button to my context menu but I keep adding buttons everything i open and close the WB.

Sub ButtonOne()

    Dim ContextMenu As CommandBar
    Call DeleteButtons
    
    Set ContextMenu = Application.CommandBars(Application.CommandBars("cell").Index + 3)
    With ContextMenu.Controls.Add(Type:=msoControlButton, Before:=1)
    .OnAction = "'" & ThisWorkbook.Name & "'!Black_Fill"
    .FaceId = 1003
    .Caption = "Format"
    .Tag = "mct1"
    End With
Call ButtonTwo

End Sub

Sub ButtonTwo()

    Dim ContextMenu As CommandBar
    Set ContextMenu = Application.CommandBars(Application.CommandBars("cell").Index + 3)
    With ContextMenu.Controls.Add(Type:=msoControlButton, Before:=2)
    .OnAction = "'" & ThisWorkbook.Name & "'!Clear"
    .FaceId = 266
    .Caption = "Clear!"
    .Tag = "mct1"
    End With
End Sub

Sub DeleteButtons()

    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl
    Set ContextMenu = Application.CommandBars("Cell")
    For Each ctrl In ContextMenu.Controls
        If ctrl.Tag = "mct1" Then
            ctrl.Delete
        End If
    Next ctrl
End Sub

Private Sub Workbook_Activate()
   
 Call ButtonOne
End Sub

Private Sub Workbook_Deactivate()
    Call DeleteButtons
End Sub

I thought I knew what i was doing..

-A

[Moomoo327]

and this

Application.CommandBars("Cell").Reset

Does not seem to be working for some reason

[Moomoo327]

and this

Application.CommandBars("Cell").Reset

Does not seem to be working for some reason

Okay so this only works when not in Page Break Preview mode? lol