Dec 04 2018 07:53 AM
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:
Dec 04 2018 08:43 AM
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
Dec 04 2018 09:05 AM