Substitution and removing of the string

[topi1]

I need a VBA for the following if someone can please help. Thank you in advance,

The data is in the column R.
Case insensitive match.
There are intermittent blank rows, but they can be removed first if absolutely necessary.

Function 1 of the VBA:
In all cells BELOW the cell that contains "TITLE2", I would like " shows" and " show" to be substituted with ":". Please notice that the preceding space is included. " shows" and " show" are part of the strings.
Function 2 of the VBA:
In all cells ABOVE the cell that contains "TITLE2", I would like " shows" and " show" as well everything to their left removed.

Here is the example.

BEFORE:

Book1
	R
1	Movies
2	TITLE1
3
4	Eros shows Star Wars
5	Strand and Regal show Superman.
6
7	TITLE2
8	Eros shows Star Wars
9	Strand and Regal show Superman.
Sheet2

AFTER (DESIRED OUTPUT):

Book1
	R
1	Movies
2	TITLE1
3
4	Eros: Star Wars
5	Strand and Regal: Superman.
6
7	TITLE2
8	Star Wars
9	Superman.
Sheet2

 

[jolivanes]

I forgot to mention, you should have "Option Compare Text" at the top of your module also for the required case insensitive match.

Option Explicit
Option Compare Text

 

[topi1]

I forgot to mention, you should have "Option Compare Text" at the top of your module also for the required case insensitive match.

Option Explicit
Option Compare Text

Thank you.