Solved

Conditionally shade rows on certain string/term

Posted on 2015-01-13
11
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 51

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
Industry Leaders: 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 51

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 51

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 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