How Can I Colour Individual Text in a Text String with VBA?

  • Do you want to analyse what people say about your product on Amazon Marketplace?

• Or the reviews that people leave on your Facebook page?

Not a problem!

You can do some basic sentiment analysis with VBA!

As an example, I’ll be analysing 1000 fine food reviews posted on Amazon. Since I am mostly interested in the very positive and the very negative ones, I’ll be using the code below to loop through each review and highlight in green the words I’ve identified as positive and in red the ones I’ve identified as negative.

That way, I can then easily determine if the general perception of my product is mostly positive or negative.

Sub ColourWordsInString()
Dim Cell As Range
Dim i As Integer
Dim prv As String
Dim word As String
Dim positive(1 To 5) As String
Dim negative(1 To 5) As String
'if it finds a certain word in a string, color it in either red or green
positive(1) = "tasty"
positive(2) = "delicious"
positive(3) = "love"
positive(4) = "great"
positive(5) = "awesome"
negative(1) = "expensive"
negative(2) = "crap"
negative(3) = "bitter"
negative(4) = "smelly"
negative(5) = "greasy"
For Each Cell In ActiveSheet.Range("J2:J" & ActiveSheet.UsedRange.Rows.Count)
prv = Cell.Value
For i = 1 To 5
word = positive(i)
If InStr(prv, positive(i)) > 0 Then
With ActiveCell
.Characters(Start:=InStr(prv, positive(i)), Length:=Len(positive(i))).Font.Color = RGB(0, 176, 80)
End With
End If
Next i
For i = 1 To 5
word = negative(i)
If InStr(prv, negative(i)) > 0 Then
With ActiveCell
.Characters(Start:=InStr(prv, negative(i)), Length:=Len(negative(i))).Font.Color = RGB(192, 0, 0)
End With
End If
Next i
Next Cell
End Sub

And voila!

You can download the full spreadsheet here – 1000 Amazon Fine Food Reviews

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.