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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.