Color the cell based on text contain..

[VBABEGINER]

Dear All,

I need help to highlight cells which contain specific text and make them color.

Example-

Col A
abc or p
pqr in op
in abc
pqr or

I want to make color only those cells which will contains " in " & " or ".

Not "in anytext" OR "anytext in" OR "or anytext" OR "anytext or".

small letter only.

Can we make any code for this please..

I have write this code..but this is not worked..

Range("A2").Select
i = 2 'start row number
For Each c In ActiveSheet.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Rng = "A" & i
If Range("A" & i).Value = " in " Then
Range(Rng).Interior.ColorIndex = 1
Else
End If
i = i + 1
Next c

 

[Scott Huish]

Why don't you just use Conditional Formatting?

Also, what does this line mean exactly?

Not "in anytext" OR "anytext in" OR "or anytext" OR "anytext or".

I'm kind of guessing that means line 3 and 4 in your example would not be colored.

 

[Rick Rothstein]

Also, what does this line mean exactly?

I'm kind of guessing that means line 3 and 4 in your example would not be colored.

If you must use a VBA solution, and if Scott's guess above is correct, and if the words "in" and "or" will always be lower case letters surrounded by spaces (never periods, commas, parentheses, etc.), then you can use this non-looping macros to do what you want (change the highlight color as desired)...

[table="width: 500"]
[tr]
	[td]Sub ColorInOr()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbYellow
  Columns("A").Replace "* in *", "", SearchFormat:=False, ReplaceFormat:=True
  Columns("A").Replace "* or *", "", SearchFormat:=False, ReplaceFormat:=True
  Application.ReplaceFormat.Clear
End Sub[/td]
[/tr]
[/table]

 

[VBABEGINER]

Hey Hi Scott,

Thanks for reply. actually conditional formatting I dont want to use..anyways..I Rick's solution work for me..

Why don't you just use Conditional Formatting?

Also, what does this line mean exactly?

I'm kind of guessing that means line 3 and 4 in your example would not be colored.

 

[VBABEGINER]

Hey Rick, Thank you so much for reply. Your solution works for me. Just need one help more, I really don't understand the entire code..Could you help me in understand..

If you must use a VBA solution, and if Scott's guess above is correct, and if the words "in" and "or" will always be lower case letters surrounded by spaces (never periods, commas, parentheses, etc.), then you can use this non-looping macros to do what you want (change the highlight color as desired)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ColorInOr()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbYellow
  Columns("A").Replace "* in *", "", SearchFormat:=False, ReplaceFormat:=True
  Columns("A").Replace "* or *", "", SearchFormat:=False, ReplaceFormat:=True
  Application.ReplaceFormat.Clear
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Hey Rick, Thank you so much for reply. Your solution works for me. Just need one help more, I really don't understand the entire code..Could you help me in understand..

Sub ColorInOr()
[B][COLOR="#FF0000"]1.[/COLOR][/B]  Application.ReplaceFormat.Clear
[B][COLOR="#FF0000"]2.[/COLOR][/B]  Application.ReplaceFormat.Interior.Color = vbYellow
[B][COLOR="#FF0000"]3.[/COLOR][/B]  Columns("A").Replace "* in *", "", SearchFormat:=False, ReplaceFormat:=True
[B][COLOR="#FF0000"]4.[/COLOR][/B]  Columns("A").Replace "* or *", "", SearchFormat:=False, ReplaceFormat:=True
[B][COLOR="#FF0000"]5.[/COLOR][/B]  Application.ReplaceFormat.Clear
End Sub

My macro is the code equivalent of using Excel's Replace dialog box... each code line duplicates a step you can take in that dialog box. This might be easier to see if we bring up the Replace dialog box and I run you through the steps one at a time. To that end, I have copied my code above and added step numbers in red so you can follow along easier. Okay, first, the dialog box... type some random text in a cell and then select that cell, then press CTRL+H to bring up the Replace dialog box. First thing you have to do is click the "Options>>" button so we can see the formatting stuff. Okay, now the steps...

1) Locate the Format button across from the "Replace with" field, then click the downward pointing triangle on that button (do not click the main part of the button, only its triangle)... a popup menu will appear. See the last item labeled "Clear Replace Format"... it is (probably) not active on your menu because you do not have a format selected (if you had, it would be active). The reason for Step #1 is to remove any preset formats as the format in VBA accumulate each time you set one and the dialog box remember the format settings from the last time they were set (whether directly in the dialog box or via VBA code).

2) This step sets the replace format to color the cell yellow when the "Find with" text is found. This is the same as clicking the Format button, choosing the Fill tab and selecting yellow as the color.

3) This step searches for the text " in " inside the cells for Column A (the asterisks in the code line are wildcards standing for zero or more arbitrary characters). Note that we are not replacing the found text with any new text... the combination of "" for the replacement text coupled with setting ReplaceFormat to TRUE means the text in the cell will be left alone.

4) This step does the same thing as Step 3 except for the text " or ".

5) This step does the same as Step #1 ... we do this so the setting we used for this operation does not get remembered for the next time the dialog box or the VBA Replace method is used.

 

[VBABEGINER]

This is excellent. Thank You so much for keen explanation..

Sub ColorInOr()
[B][COLOR=#FF0000]1.[/COLOR][/B]  Application.ReplaceFormat.Clear
[B][COLOR=#FF0000]2.[/COLOR][/B]  Application.ReplaceFormat.Interior.Color = vbYellow
[B][COLOR=#FF0000]3.[/COLOR][/B]  Columns("A").Replace "* in *", "", SearchFormat:=False, ReplaceFormat:=True
[B][COLOR=#FF0000]4.[/COLOR][/B]  Columns("A").Replace "* or *", "", SearchFormat:=False, ReplaceFormat:=True
[B][COLOR=#FF0000]5.[/COLOR][/B]  Application.ReplaceFormat.Clear
End Sub

My macro is the code equivalent of using Excel's Replace dialog box... each code line duplicates a step you can take in that dialog box. This might be easier to see if we bring up the Replace dialog box and I run you through the steps one at a time. To that end, I have copied my code above and added step numbers in red so you can follow along easier. Okay, first, the dialog box... type some random text in a cell and then select that cell, then press CTRL+H to bring up the Replace dialog box. First thing you have to do is click the "Options>>" button so we can see the formatting stuff. Okay, now the steps...

1) Locate the Format button across from the "Replace with" field, then click the downward pointing triangle on that button (do not click the main part of the button, only its triangle)... a popup menu will appear. See the last item labeled "Clear Replace Format"... it is (probably) not active on your menu because you do not have a format selected (if you had, it would be active). The reason for Step #1 is to remove any preset formats as the format in VBA accumulate each time you set one and the dialog box remember the format settings from the last time they were set (whether directly in the dialog box or via VBA code).

2) This step sets the replace format to color the cell yellow when the "Find with" text is found. This is the same as clicking the Format button, choosing the Fill tab and selecting yellow as the color.

3) This step searches for the text " in " inside the cells for Column A (the asterisks in the code line are wildcards standing for zero or more arbitrary characters). Note that we are not replacing the found text with any new text... the combination of "" for the replacement text coupled with setting ReplaceFormat to TRUE means the text in the cell will be left alone.

4) This step does the same thing as Step 3 except for the text " or ".

5) This step does the same as Step #1 ... we do this so the setting we used for this operation does not get remembered for the next time the dialog box or the VBA Replace method is used.

 

[VBABEGINER]

Hi Rick,

Can I use, specific list with this..Like this..

Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.Color = vbGreen
Columns("A").Replace "* -" & mylist&, "", SearchFormat:=False, ReplaceFormat:=True
Columns("A").Replace "* -" & mylist&, "", SearchFormat:=False, ReplaceFormat:=True
Application.ReplaceFormat.Clear

It mean, the word which are mentioned in "mylist" will only made the color. The words which are like not in this list will gets colored..

Can we do such like this..

If you must use a VBA solution, and if Scott's guess above is correct, and if the words "in" and "or" will always be lower case letters surrounded by spaces (never periods, commas, parentheses, etc.), then you can use this non-looping macros to do what you want (change the highlight color as desired)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ColorInOr()
  Application.ReplaceFormat.Clear
  Application.ReplaceFormat.Interior.Color = vbYellow
  Columns("A").Replace "* in *", "", SearchFormat:=False, ReplaceFormat:=True
  Columns("A").Replace "* or *", "", SearchFormat:=False, ReplaceFormat:=True
  Application.ReplaceFormat.Clear
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBABEGINER]

Any luck on this..please

 

[Scott Huish]

You would have to loop through the values. Is mylist a named range, an array or what?