Home

Help with formula or macro!

LearningVBA1430
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:

Before.PNG

 

Want formula or macro to produce:

After.PNG

2 Replies
Highlighted

Hi,

 

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
    Next
    
    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!