INSTR to find string value, need function to format only the value

[Peteor]

Hello! I am using the INSTR function to find all cells in a row where the string "Shall" occurs, and have 2 questions.

1. How do I make what it returns not case specific?
And
2. How do I apply formats to ONLY the word "Shall" (not the rest of the cell contents)?

Below is how far I have gotten here:

Sub Macro1()
Dim Cel As Range
Dim Rng As Range

Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("B2:B10000")

For Each Cel In Rng
If InStr(Cel.Value, "Shall") > 0 Then


End Sub

 

[Fluff]

To make it case insensitive use

If InStr(1, Cel.Value, "Shall", vbTextCompare) > 0 Then

What format do you want to apply?
Also is the cell text or a formula?

 

[Peteor]

Thank you again Fluff! You rock!

I want to make "Shall" all caps, bold, and change the font color to red.

 

[Peteor]

Thank you again Fluff! You rock!

I want to make "Shall" all caps, bold, and change the font color to red.

Cell is text

 

[Fluff]

This will make it bold & red

Sub Peteor()
   Dim Cel As Range, Rng As Range
   Dim x As Long
   
   Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("B2:B10000")
   
   For Each Cel In Rng
      x = InStr(1, Cel.Value, "Shall", vbTextCompare)
      If x > 0 Then
         With Cel.Characters(x, 5).Font
            .Bold = True
            .Color = vbRed
         End With
      End If
   Next Cel
End Sub

 

[Fluff]

This will change the word to caps as well

Sub Peteor()
   Dim Cel As Range, Rng As Range
   Dim x As Long
   
   Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("B2:B10000")
   
   For Each Cel In Rng
      x = InStr(1, Cel.Value, "Shall", vbTextCompare)
      If x > 0 Then
         Cel = Replace(Cel, "Shall", "SHALL", , , vbTextCompare)
         With Cel.Characters(x, 5).Font
            .Bold = True
            .Color = vbRed
         End With
      End If
   Next Cel
End Sub

 

[Peteor]

This helps, however I think we are missing a couple small tweaks.

This works when "Shall is the 1st word in the Cel, but what about when it is not?

Also, when I ran the second one you sent, it changed all of the text in the Cel, rather than just "Shall".

Any ideas?

 

[Fluff]

This is what I get when I use that code.

+Fluff 1.xlsm
	A	B
4
5	I shall do it	I SHALL do it
6	Shall I do it	SHALL I do it
7	Yes it shall be	Yes it SHALL be
8	Yes	Yes
9	No you shall not	No you SHALL not
10
Main

The text colour hasn't come through but it is red

 

[Peteor]

You are correct. So sorry! I made a mistake in transposing!

 

[Peteor]

You are correct. So sorry! I made a mistake in transposing!

Thank you so much for always helping me!