[VBA] Highlight all occurances of specific text AND insert a blank row beneath

[quercus]

Hello all,

I have a worksheet that has various text names in Column G. I would like to highlight all cells in column G that contain the text "T2", and then insert a blank row beneath.

Many thanks
quercus

 

[Smitty]

Well, the first part you can do with Conditional Formatting.

For the second, does this do what you need:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>            <SPAN style="color:#00007F">For</SPAN> i = Cells(Rows.Count, "G").End(xlUp).Row <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>                <SPAN style="color:#00007F">If</SPAN> Cells(i, "G").Value = "T2" <SPAN style="color:#00007F">Then</SPAN> Cells(i, "G").Offset(1).EntireRow.Insert xlShiftDown<br>            <SPAN style="color:#00007F">Next</SPAN> i<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[quercus]

Hey Smitty,

many thanks for the swift response!

Yep already got my conditional formatting highlighting the cells, but I have many spreadsheets so just wondered whether this could be inbuilt into the macro in the first instance. Not an issue if not, I can use the conditional formatting prior to the running the VBA, it will just add an extra step making the process less efficient.

The code you provided doesn't appear to work - is it looking strictly for the text "T2" - I need it to identify cells that contain "t2" within the text, e.g. MX241T2?

Quercus

 

[Smitty]

I need it to identify cells that contain "t2" within the text, e.g. MX241T2?

You can adjust it by checking if that string is in the cell:

if InStr(Cells(i, "G").Value ,"T2") Then

 

[quercus]

Sweet works a treat - am new to this VBA lark but picking up tips fast.

Thank you muchly