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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top