PDA

View Full Version : highlight the cell with "peter"



clarksonneo
02-11-2011, 11:02 AM
Hi,


I want a marco that can highlight the cells that contain the word "peter".
Eg, Peter A, Peter B.

Could you please write me the marco?

The following picture can show the expected result.

Thanks

kemas
02-11-2011, 12:37 PM
try this

in column a your data
in column b this formula
=IF(ISNUMBER(FIND("peter";A1;1));TRUE;FALSE)

and this code

Dim myr As Range
Dim myc As Range
Set myr = Range("a1:a100")
For Each myc In myr
If myc.Offset(0, 1) = "True" Then
myc.Interior.ColorIndex = 6
End If
Next myc

mancubus
02-11-2011, 12:46 PM
if you have only one condition, try:


Sub cond_format()

Dim rng As Range, cll As Range
Dim crit

Set rng = Range("B2:B100") ' change to suit
crit = "peter"

For Each cll In rng
If InStr(UCase(cll), UCase(crit)) > 0 Then
cll.Interior.Color = vbYellow 'change to suit
End If
Next

End Sub

clarksonneo
02-11-2011, 06:37 PM
if you have only one condition, try:


Sub cond_format()

Dim rng As Range, cll As Range
Dim crit

Set rng = Range("B2:B100") ' change to suit
crit = "peter"

For Each cll In rng
If InStr(UCase(cll), UCase(crit)) > 0 Then
cll.Interior.Color = vbYellow 'change to suit
End If
Next

End Sub



Hi,

Thank you for your code.
I have 1 more problem.

If the cell's value is "Peterson" or "McPeter", the cell will be highlighted.
I just want to highlight the cell with "Peter".

Could you please tell me show the a revised version of code so that I can obtain the result I want?

Thanks

frank_m
02-12-2011, 12:26 AM
Requiring that the length of the match be equal to the length of the criteria, seems to work.
Sub cond_format()

Dim rng As Range, cll As Range
Dim crit

Set rng = Range("B2:B100") ' change to suit
crit = "peter"

For Each cll In rng
If InStr(UCase(cll), UCase(crit)) > 0 And Len(cll) = Len(crit) Then
cll.Interior.Color = vbYellow 'change to suit
End If
Next

End Sub

clarksonneo
02-12-2011, 12:50 AM
Requiring that the length of the match be equal to the length of the criteria, seems to work.
Sub cond_format()

Dim rng As Range, cll As Range
Dim crit

Set rng = Range("B2:B100") ' change to suit
crit = "peter"

For Each cll In rng
If InStr(UCase(cll), UCase(crit)) > 0 And Len(cll) = Len(crit) Then
cll.Interior.Color = vbYellow 'change to suit
End If
Next

End Sub
thinks for your reply

Some cells may contain the family name as well, eg Peter A, Peter B.
How can I highlight those cells?

Eg, highlight Peter A, etc.
Not hightlight Peterson A, McPeter B, etc.

Thank you, I think this is the last question in this topic I ask.

shrivallabha
02-12-2011, 12:53 AM
Probably this Mancubus' edited code is what you are after:
Sub cond_format()

Dim rng As Range, cll As Range
Dim crit

Set rng = Range("B2:B100") ' change to suit
crit = "peter"

For Each cll In rng
If InStr(UCase(cll), UCase(crit)) > 0 Then
If Len(cll) = Len(crit) Then
cll.Interior.Color = vbYellow 'change to suit
Else
MyChar = Split(UCase(cll), " ")
For i = LBound(MyChar) To UBound(MyChar)
If UCase(crit) = MyChar(i) Then
cll.Interior.Color = vbYellow
End If
Next i
End If
End If
Next

End Sub