Highlight row based on cell value

[effendrew]

I need some help and have searched the forum and found partial solutions. I have an existing macro I wrote and need to add a row highlight (cells A-F) based off specific names listed in column A. There are 5 specific names so I figured I could list it 5 times each with the specific name. the cell highlight color can be yellow for now.
I haven't had success in reverse engineering this.

This needs to be a macro/VBA not conditional formatting. we run this macro 5 times each day on a new .csv file sent in.
Current workflow is to "open" the Marco File then open the .csv file and the run the macro from Macro File.
It works well with the users, just now need to add the highlighting for specific names that appear in column A

thank you
Drew

 

[richh]

Can't use conditional formatting..? That blows.

Loop through the rows and then change the background color

Dim ws   as worksheet
Dim lRow as Long

Set ws = Application.Thisworkbook.worksheets("YourSheetName")

lRow = ws.cells(Rows.Count, 1).End(xlUp).Row

For i = 2 to lRow
    ws.Range("A" & i & ":[Column]" & i).Select
    
    Select Case ws.cells(i,1)
        Case "Name1"
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535 'This is yellow; find a color sheet online for alternate colors'
                .TintAndShade = 0
                 .PatternTintAndShade = 0
            End With
        Case "Name2"
            'Repeat and change color in code above'
        Case "Name3"
        Default
            'Blah blah blah'
    End Select
next i

 

[effendrew]

this I what I currently have and I am just not sure how to add the other names without errors.
The Name 1 in blue is one of 5 names I am trying to highlight.


Sub Yellow()
Dim Cell As Range, Addr As String
With Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set Cell = .Find("*NAME 1*", , xlValues, , , , False, , False)
If Not Cell Is Nothing Then
Addr = Cell.Address
Do
Cell.Resize(, 6).Interior.Color = 65535
Set Cell = .FindNext(Cell)
Loop While Not Cell Is Nothing And Cell.Address <> Addr
End If
End With
End Sub

 

[richh]

You just change the case names. You don't need a sub for each color, just a case statement. If the cell is "name 1" , range interior color= x. If cell = "name2", color = y.

 

[effendrew]

You just change the case names. You don't need a sub for each color, just a case statement. If the cell is "name 1" , range interior color= x. If cell = "name2", color = y.

Agree I don't want to have separate routines, just trying yo figure out how to do this, there seems to be many ways to skin a cat on this one. LIST, IFLIST, SELECT CASE, SET FIND
I have been looking at CASE Statements, it seems to be the easiest but I just cannot get the multiple Names to work.