Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Conditionally shade rows on certain string/term

Posted on 2015-01-13
11
Medium Priority
?
74 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 28

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 53

Accepted Solution

by:
Rgonzo1971 earned 1200 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 53

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 800 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 53

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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.

927 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