EXCEL VBA macro for putting a style to a range and then ucase

Question

i'm newbie, sorry in advance for myy long question

So, i have two macros (one recorded and pasted in personal macro) and other i found in google

The first one, with my selection fills the color to orange and adds bolds borders

The second one with the selection, upercases all the range.

However, when i run this two macro together with another sub (calling the subs) the text does not shows up, i need to change of cell then select again and run the macro again in order to function.

Sub text ()
Dim rng As Range
Dim sAddr As String
Set rng = Selection

Selection.Merge    
    ActiveCell.FormulaR1C1 = _
        "=""additional due for "" & TEXT(TODAY(),""MMMM "") & ""end of month"""   
   sAddr = rng.Address

    rng = Evaluate("index(upper(" & sAddr & "),)")

    Selection.NumberFormat = "General"

End Sub

Then the filling up sub (which is a little long)

Sub ORANGE()


Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        'CAMBIO 2
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
     Selection.NumberFormat = "General"    

End Sub

The way i use both macros is simply calling first ORANGE and then TEXT, beacuse the other way does not works, when i try them in VBA run macro option it works fine.

When i use the button in the ribbon i need to change of cell, select it again and it will work. i use this macro a lot but it simply makes me repeat it each time.

Does anybody knows who to perform both task at once without the result being an empty orange cell?

Thanks!

Start by reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/62576) — Ken White, Dec 10 '19 at 01:48

score 0 · Accepted Answer · answered Dec 10 '19 at 03:16

Try this. Read comments inside the code:

Public Sub AddTextAndFormat()

    Dim selectedRange As Range

    Set selectedRange = Selection

    ' Merges the selection
    selectedRange.Merge
    ' Adds the formula to the first selection's cell
    selectedRange.Formula = "=""additional due for "" & TEXT(TODAY(),""MMMM "") & ""end of month"""
    ' Uppercase that first cell
    selectedRange.Cells(1, 1).Value = UCase$(selectedRange.Cells(1, 1).Value)

    ' Apply formats
    With selectedRange
        .Font.Bold = True

        ' Borders:
        .BorderAround LineStyle:=xlContinuous, ColorIndex:=0, Weight:=xlMedium

        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders(xlInsideVertical).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone

        ' Other format:
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        'CAMBIO 2
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        '.MergeCells = False  -> This line unmerges the first cells merge
        .NumberFormat = "General"
    End With

    With selectedRange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub

EXCEL VBA macro for putting a style to a range and then ucase

1 Answers1