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
	Cell.Activate
	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
	Cell.Activate
	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

view raw CellInR_ColourWordsInString.bas hosted with ❤ by GitHub

And voila!

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

Happy VBA coding!