Conditionally shade rows on certain string/term

Dear Experts:

below macro basically searches for the occurrence of a term/string in line 16 and if found shades the corresponding row red.

Works fine but I know would like to search for 9 digit numbers (separated by hypens) that are present in the following form:

##-###-##-##\##-###-##-##.jpg

These sub-strings are only part of the cell contents (network paths)

C:\MyDocuments\Graphics\90-234-57-11\90-234-57-11.jpg or
C:\MyDocuments\Graphics\84-439-24-33\84-439-24-33.jpg or
C:\MyDocuments\Graphics\45-434-99-11\45-434-99-11.jpg

So could somebody please help me to shade all the rows of the current sheet where the following substrings occur:

##-###-##-##\##-###-##-##.jpg

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

Regards, Andreas



Sub Colour_Specific_Rows()
Dim rng As Range
Dim Rw As Range
Dim LastCol As Long
Dim lastRow As Long
Dim ans

      LastCol = Cells(1, Columns.count).End(xlToLeft).Column

      lastRow = Cells(Rows.count, 1).End(xlUp).Row

      Set rng = Range("A1", Cells(lastRow, LastCol))

      For Each Rw In rng.Rows

               ans = Application.CountIf(Rw, "*_1.jpg*")

               If ans > 0 Then
                   Rw.Interior.Color = RGB(242, 220, 219)
               End If
       Next Rw
End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

Sub Colour_Specific_Rows()
Dim rng As Range
Dim Rw As Range
Dim c As Range
Dim LastCol As Long
Dim lastRow As Long


      LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

      lastRow = Cells(Rows.Count, 1).End(xlUp).Row

      Set rng = Range("A1", Cells(lastRow, LastCol))

      For Each Rw In rng.Rows
            For Each c In Rw.Cells
               If c Like "*##-###-##-##\##-###-##-##.jpg*" Then
                   Rw.EntireRow.Interior.Color = RGB(242, 220, 219)
                   Exit For
               End If
            Next
       Next Rw
End Sub

Open in new window

Regards
0
 
MacroShadowCommented:
Why not use conditional formatting?
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi MacroShadow,

I could of course but then how would the formula to be entered look like?

Regards, Andreas
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Andreas HermleTeam leaderAuthor Commented:
Hi Rgonzo,

thank you very much for your swift help. I am afraid to tell you that it somehow does not work. Nothing gets shaded whatsoever. Any idea why?

Regards, Andreas
0
 
Rgonzo1971Commented:
Could you send a dummy?
0
 
Andreas HermleTeam leaderAuthor Commented:
okay, I will do this shortly. Thanks, regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Rgonzo,

uups, just tried the macro out on a new sample file and guess what, it worked, great!! I will do some more testing and then let you know.

Regards, Andreas
0
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
You could use conditional formatting using the COUNTIF function like so:
=COUNTIF($A2,"*??-???-??-??\??-???-??-??.jpg")
conditional formatting approach
Regards,
-Glenn
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Glenn,

thank you very much for your post and solution. I will try it and then get back with a feedback.

Thank you, regards, Andreas
0
 
Rgonzo1971Commented:
I haven't used the countif solution because it does not test whether the characters are numbers

but of course with such a pattern the probability of a false answer could be minimal

Regards
0
 
Andreas HermleTeam leaderAuthor Commented:
Dear Rgonzo, thank you very much to bring this to my attention but as you said the pattern I am working with is unambigous.

Anyhow thank you very much to both of you for the VBA and non-VBA solution. I really appreciate your professional work/help.

 Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.