When making my selection in the drop down, I need it to highlight in color all pertaining to my selection?

I have created a drop down list utilizing data validation. When I make a selection in the drop down list, I need it to highlight all data pertaining to my selection. For example, there are 10 names in my list. When I select one of the names, I need the entire spreadsheet to highlight in color every place where that name appears.





Thank you kindly!When making my selection in the drop down, I need it to highlight in color all pertaining to my selection?
The following macro should work for you.





It assumes that your drop down validation list is in A1.


If not, change '[A1]' in Line 14 to your cell reference, i.e. [C12].





Copy the this macro to the clipboard:





Private Sub Worksheet_Change(ByVal Target As Range)


Dim LastColumn As Integer


Dim LastRow As Long


Dim LastCell As Range


If WorksheetFunction.CountA(Cells) %26gt; 0 Then


LastRow = Cells.Find(What:=';*';, After:=[A1], _


SearchOrder:=xlByRows, _


SearchDirection:=xlPrevious).Row


LastColumn = Cells.Find(What:=';*';, After:=[A1], _


SearchOrder:=xlByColumns, _


SearchDirection:=xlPrevious).Column


End If


For i = 1 To LastRow


For j = 1 To LastColumn


If [A1] = ';'; Then


Cells(j, i).Interior.ColorIndex = xlNone


ElseIf Cells(j, i).Value = [A1] Then


Cells(j, i).Interior.ColorIndex = 6


Else


Cells(j, i).Interior.ColorIndex = xlNone


End If


Next j


Next i


End Sub








Press ALT + F11





Double click on the sheet that you want the macro to function in. (Microsoft Excel Objects - mid upper left).





Paste the macro into the module area to the right.





Close back to Excel.





Select a name in the combobox. All instances in the worksheet should highlight to light yellow. If you delete the name in A1 all highlighting will be cleared from the worksheet.When making my selection in the drop down, I need it to highlight in color all pertaining to my selection?
In the change event for that dropdown, change the format for any cell with that name.