Help with formula or macro!

New Contributor

Looking for assistance on a formula or macro. I have lots of data and I fill in a various color on certain numbers, leaving others with no color. All the non-colored cells need brackets. Best way to be able to select a full row - and execute???


Would start with:



Want formula or macro to produce:


2 Replies



I would suggest this code:

Sub SquareBracketsBeforeAndAfterTexts()
    'By Haytham Amairah
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Dim step1 As String
    Dim step2 As String
    Dim cell As Range
    For Each cell In Selection
        If cell.Interior.ColorIndex = -4142 Then
            step1 = Application.WorksheetFunction.Replace(cell, 1, 0, "[")
            step2 = Application.WorksheetFunction.Replace(step1, Len(step1) + 1, 0, "]")
            cell.Value = step2
        End If
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub


But you need to select the range of cells before you run this code.


Hope that helps

That does the trick! Thank you Haytham, you know your stuff!