PDA

View Full Version : Solved: Highlight unlisted names



YasserKhalil
11-19-2010, 06:19 PM
Hi everybody
I have attached a workbook
I want to highlight the names which are not listed in the list of names

p45cal
11-19-2010, 06:45 PM
I have attached a workbookno you haven't

YasserKhalil
11-19-2010, 06:52 PM
I'm sorry
There's an error as for uploading the file

p45cal
11-19-2010, 07:13 PM
see conditional formatting in E2:G5.

YasserKhalil
11-20-2010, 04:01 AM
Thank you very much for this great solution
Could it be done by code in Workksheet_Change?

p45cal
11-20-2010, 04:52 AM
Yes, but why is it necessary?

YasserKhalil
11-20-2010, 05:10 AM
why is it necessary?
To learn more using VBA and make the file more efficient , smaller in size I think

p45cal
11-20-2010, 10:06 AM
For the workbook you supplied (remove/change any conditional formatting as that will override what the macro does if it wants to change the same aspect of formatting as the macro does.:Private Sub Worksheet_Change(ByVal Target As Range)
Set BigList = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Set SmallList = Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))
'BigList.Select
'SmallList.Select
For Each cll In SmallList.Cells
Set FoundMe = Nothing
Set FoundMe = BigList.Find(What:=cll.Value, LookIn:=xlValues, lookat:=xlWhole)
If FoundMe Is Nothing Then
cll.Interior.ColorIndex = 46
Else
cll.Interior.ColorIndex = xlNone
End If
Next cll
End Sub

YasserKhalil
11-21-2010, 03:39 AM
Thanks a lot Mr. p45cal (http://www.vbaexpress.com/forum/member.php?u=3494)
You are agreat person

I need a bit change to let the blank cells with no colour
I tried to clear a name and I found that the cells with colours still coloured
I want to remove colour from blank ones

Private Sub Worksheet_Change(ByVal Target As Range)
Set BigList = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Set SmallList = Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))
For Each cll In SmallList.Cells
Set FoundMe = Nothing
Set FoundMe = BigList.Find(What:=cll.Value, LookIn:=xlValues, lookat:=xlWhole)
If FoundMe Is Nothing Then
cll.Interior.ColorIndex = 46
cll.Offset(0, 1).Interior.ColorIndex = 46
cll.Offset(0, 2).Interior.ColorIndex = 46
Else
cll.Interior.ColorIndex = xlNone
cll.Offset(0, 1).Interior.ColorIndex = xlNone
cll.Offset(0, 2).Interior.ColorIndex = xlNone
End If
Next cll
End Sub

p45cal
11-21-2010, 03:58 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Set BigList = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Set SmallList = Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))
For Each cll In SmallList.Cells
Set FoundMe = BigList.Find(What:=cll.Value, LookIn:=xlValues, lookat:=xlWhole)
If FoundMe Is Nothing And Not cll.Value = Empty Then
cll.Resize(, 3).Interior.ColorIndex = 46
Else
cll.Resize(, 3).Interior.ColorIndex = xlNone
End If
Next cll
End Sub

YasserKhalil
11-21-2010, 04:40 AM
It doesn't do as I expect
Look!
Try typing a name which isnot in the list and clear that name you will find that the cells are still coloured

p45cal
11-21-2010, 06:20 AM
It doesn't do as I expect
Look!
Try typing a name which isnot in the list and clear that name you will find that the cells are still coloured I tested it before posting and it worked. Are you sure you've removed all conditional formatting from those cells? CF will override anything the macro does.
If necessary I will attach the file but I'm not on the same computer at the moment.

YasserKhalil
11-21-2010, 10:15 AM
Are you sure you've removed all conditional formatting from those cells? CF will override anything the macro does.
Yes, I'm sure


If necessary I will attach the file
Yes, please

p45cal
11-21-2010, 11:59 AM
I think I see what may have been happening; this only happened if you cleared cells at the bottom of the shorter list, not in the middle of the list. Try:Private Sub Worksheet_Change(ByVal Target As Range)
Static OldSmallList As Range
Set BigList = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
Set SmallList = Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))
Set SmallList = Union(IIf(OldSmallList Is Nothing, SmallList, OldSmallList), SmallList)
For Each cll In SmallList.Cells
Set FoundMe = BigList.Find(What:=cll.Value, LookIn:=xlValues, lookat:=xlWhole)
If FoundMe Is Nothing And Not cll.Value = Empty Then
cll.Resize(, 3).Interior.ColorIndex = 46
Else
cll.Resize(, 3).Interior.ColorIndex = xlNone
End If
Next cll
Set OldSmallList = Range(Cells(2, 5), Cells(Rows.Count, 5).End(xlUp))
End Sub

YasserKhalil
11-22-2010, 08:22 AM
Yes this is the perfect code
Thank you very much
I know I have disturbed you..But you are great and patient
Thanks again for your help