Highlight row based on cell value

effendrew

New Member
Joined
Oct 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2011
Platform
  1. MacOS
  2. Mobile
  3. Web
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

richh

Board Regular
Joined
Jun 24, 2007
Messages
105
Can't use conditional formatting..? That blows.

Loop through the rows and then change the background color

VBA Code:
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

New Member
Joined
Oct 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2011
Platform
  1. MacOS
  2. Mobile
  3. Web
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

Board Regular
Joined
Jun 24, 2007
Messages
105
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

New Member
Joined
Oct 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2011
Platform
  1. MacOS
  2. Mobile
  3. Web
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,942
Messages
5,543,126
Members
410,583
Latest member
jgalin
Top