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

 

[VBABEGINER]

Thanks Scott for your reply.

I define this list in Name Manager. SO basically it is Named Range..So only those gets highlight apart to that should not highlight..

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

 

[Scott Huish]

Sub ColorInOr()
Dim c as Range
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.Color = vbYellow
For Each c In Range("mylist")
    Columns("A").Replace "* " & c & " *", "", SearchFormat:=False, ReplaceFormat:=True
Next
Application.ReplaceFormat.Clear
End Sub

 

[VBABEGINER]

Thank You so much dear...
This works for me...

one more additional, if it is possible..Can I minimize this code..

Dim MLst1 As Range
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.Color = vbGreen
For Each MLst1 In Range("Mylist")
Columns("A").Replace "* " & MLst1 & " *", "", SearchFormat:=False, ReplaceFormat:=True
Next
Application.ReplaceFormat.Clear


Dim MLst2 As Range
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.Color = vbGreen
For Each MLst2 In Range("Mylist")
Columns("A").Replace "* " & MLst2, "", SearchFormat:=False, ReplaceFormat:=True
Next
Application.ReplaceFormat.Clear

Where MLst = your c variable..

Sub ColorInOr()
Dim c as Range
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.Color = vbYellow
For Each c In Range("mylist")
    Columns("A").Replace "* " & c & " *", "", SearchFormat:=False, ReplaceFormat:=True
Next
Application.ReplaceFormat.Clear
End Sub

 

[Scott Huish]

This would seem to be inconsistent with your original post.

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".

That would seem to indicate that you wouldn't want to color cells that end in your word.