Searching for a string in data list and determine column using VBA

Dear Experts:

below code highlights the rows where the value in Column 'C' (item codes) starts with the string '03-'.

The item codes starting with the string '03-' are located in just one column but they may be in a different column in another data list.

So could somebody please help me rewrite this code so that ...

... the macro searches  for the first occurrence of the string '03-' in the data list and sets that column as a variable replacing the hard coded "C" in line 4

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas


Sub Hightlight_Rows_On_Condition()
    Dim row As Range
    For Each row In ActiveSheet.UsedRange.Rows
    If InStr(1, row.Cells(1, "C"), "03-") Then
        row.Interior.ColorIndex = 6
    End If
    Next row
End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Sub Hightlight_Rows_On_Condition()
    Dim row As Range
    Dim strColumn As String
    
    strColumn = InputBox("Please enter the column letter")
    If StrPtr(strColumn) = 0 Then
        MsgBox "You clicked 'Cancel'"
        Exit Sub
    ElseIf strColumn = "" Then
        MsgBox "You didn't enter anything"
        Exit Sub
    End If
    
    For Each row In ActiveSheet.UsedRange.Rows
    If InStr(1, row.Cells(1, strColumn), "03-") Then
        row.Interior.ColorIndex = 6
    End If
    Next row
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
In rereading your question I'm not sure that I interpreted it correctly. I assume now that you are always looking for "03-", and you want the column it looks in to be a variable. Is that correct?
0
byundtMechanical EngineerCommented:
I modified your macro so it would look for a cell containing a value that started with 03-. The macro then searched every cell in that column beginning with 03- and colored it.
Sub Hightlight_Rows_On_Condition()
Dim cel As Range, rg As Range, row As Range
Set cel = ActiveSheet.UsedRange.Find("03-*", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows)
Set rg = Intersect(cel.EntireColumn, ActiveSheet.UsedRange)
For Each row In rg.Rows
If InStr(1, row.Value, "03-")=1 Then
    row.Interior.ColorIndex = 6
End If
Next row
End Sub

Open in new window

0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Andreas HermleTeam leaderAuthor Commented:
Dear Martin,

thank you very much for your swift and professional help. My question was not unequivocal, I have to admit, actually I was looking for a solution provided by Brad.  Although his solution still requires a bit tweaking.
NEVERTHELESS, your approach is very good, It works just fine and I will integrate your approach in another macro I will be running.

Brad: Thank you very much for your professional help. The macro works just fine although it would be great if you could tweak it so that the rows (not the entire row, but just the used range of the rows) where this criterion is met gets shaded, not only the respective cells.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
byundtMechanical EngineerCommented:
Andreas,
I modified the macro so it highlights the entire row of data when a match is found.
Brad
Sub Hightlight_Rows_On_Condition()
Dim cel As Range, rg As Range, rgData As Range
Dim i As Long, n As Long
Set rgData = ActiveSheet.UsedRange
Set cel = rgData.Find("03-*", LookAt:=xlWhole, LookIn:=xlValues, SearchOrder:=xlByRows)
Set rg = Intersect(cel.EntireColumn, rgData)
n = rgData.Rows.Count
For i = 1 To n
    If InStr(1, rg.Cells(i, 1).Value, "03-") = 1 Then
        rgData.Rows(i).Interior.ColorIndex = 6
    End If
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Andreas HermleTeam leaderAuthor Commented:
Dear both,

since I can use both codes, I have split the points!

Thank you very much for your great, professional and swift help. I really appreciate it.

Good job!

Regards, Andreas
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.