Solved

Conditionally shade rows on certain string/term

Posted on 2015-01-13
11
68 Views
Last Modified: 2015-01-14
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

0
Comment
Question by:AndreasHermle
11 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40546336
Why not use conditional formatting?
0
 

Author Comment

by:AndreasHermle
ID: 40546361
Hi MacroShadow,

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

Regards, Andreas
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 300 total points
ID: 40546367
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:AndreasHermle
ID: 40546386
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40546428
Could you send a dummy?
0
 

Author Comment

by:AndreasHermle
ID: 40546442
okay, I will do this shortly. Thanks, regards, Andreas
0
 

Author Comment

by:AndreasHermle
ID: 40546479
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 200 total points
ID: 40547381
You could use conditional formatting using the COUNTIF function like so:
=COUNTIF($A2,"*??-???-??-??\??-???-??-??.jpg")
conditional formatting approach
Regards,
-Glenn
0
 

Author Comment

by:AndreasHermle
ID: 40548382
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40548387
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
 

Author Closing Comment

by:AndreasHermle
ID: 40548775
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now