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

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!
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies