Solved

Conditionally shade rows on certain string/term

Posted on 2015-01-13
11
66 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 26

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 48

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
 

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 48

Expert Comment

by:Rgonzo1971
ID: 40546428
Could you send a dummy?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 48

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

759 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

17 Experts available now in Live!

Get 1:1 Help Now