CODEWITHSUNDEEP
vbaexcelformulahighlight
J. Doe
J. Doe

Reputation: 23

Format and Color Text in Excel Between Certain SubStrings

I want to highlight some text (make it red and bold) in excel. This should be done automatically with a formula under certain conditions. The text is in this format:

<file size="99999" index="0" tid="2893892389283">picture.jpg</file><file size="65444557" index="0" tid="5636346466">movie.avi</file><file size="12135" index="0" tid="43743766433">textfile.txt</file>

All of this stuff is in one cell. Now i want to highlight the number after "size=" and the name of the file. For example, in the first field i want to make 99999 and pictures.jpg red and bold.

I have a lot of text in this format in my excel document and the only way is to make it anyway automatically.

Here is an example. On the right side is the result which I need: Here is an example. On the right side is the result which i need

Upvotes: 2

Views: 1010

Answers (1)

Vityata
Vityata

Reputation: 43585

As @ScottCraner mentioned in the comments, this is not achieveable with a formula. However, with the options are limitless.

This achieves 50% of the "task":

enter image description here

Public Sub FormatAndColor()

    Range("B1") = Range("A1")

    Dim lookFor As String: lookFor = "size="""
    Dim i As Long
    Dim lenLookFor As Long: lenLookFor = Len(lookFor)

    For i = 1 To Len(Range("B1"))
        With Range("B1")
            If .Characters(i, lenLookFor).Text = lookFor Then
                While IsNumeric(.Characters(i + lenLookFor, 1).Text)
                    With .Characters(i + lenLookFor, 1).Font
                    .Bold = True
                    .Color = vbRed
                    End With
                    i = i + 1
                Wend
            End If
        End With
    Next i

End Sub

This is what the code does:

  • copies the input from A1 to B1:
  • loops through every character of the present string
  • if it finds that the String and the next N strings are equal to size=", it goes into a new loop.
  • in the new loop if bolds and colors every numeric character

For the other 50% of the "task" you may apply similar logic - look for > and color it until you do not find the next openning tag. Just be careful not to enter endless loop with the last >. This is why the i < Len(Range("B1")) is present there:

Public Sub FormatAndColor()

    Range("B1") = Range("A1")

    Dim lookFor As String: lookFor = ">"
    Dim i As Long
    Dim lenLookFor As Long: lenLookFor = Len(lookFor)

    For i = 1 To Len(Range("B1"))
        With Range("B1")
            If .Characters(i, lenLookFor).Text = lookFor Then
                While .Characters(i + lenLookFor, 1).Text <> "<" And i < Len(Range("B1"))
                    .Characters(i + lenLookFor, 1).Font.Bold = True
                    .Characters(i + lenLookFor, 1).Font.Color = vbRed
                    i = i + 1
                Wend
            End If
        End With
    Next i

End Sub

enter image description here

At the end, if you unite the two loops and make sure to update the lookFor and the lenLookFor, you would get what you need:

enter image description here

Upvotes: 3

Related Questions