Solved

How to modify vba code to search cell for text with wildcards?

Posted on 2014-12-03
8
455 Views
Last Modified: 2014-12-03
The below code will search cell A1 for the text “wip” and “PC” and highlight the text, but I also need it to find all dates.  I need to search text for a date equal to this format ##/##/##.

Example of data:
02/02/13 14:00:21 (john.doe):
09/21/14 09:00:21 (jane.doe):
12/14/15 16:00:21 (john.doe):

Sub HighLightText()
Dim intStart As Integer
Dim intEnd As Integer
intStart = 1
Do
    intStart = InStr(intStart, LCase(Range("A1").Value), "wip")
    If intStart > 0 Then
        Range("A1").Characters(intStart, 3).Font.Color = vbBlue
        intStart = intStart + 1
    End If
Loop Until intStart = 0

intStart = 1
Do
    intStart = InStr(intStart, LCase(Range("A1").Value), "PC")
    If intStart > 0 Then
        Range("A1").Characters(intStart, 2).Font.Color = vbRed
        intStart = intStart + 1
    End If
Loop Until intStart = 0

End Sub

Open in new window

0
Comment
Question by:kbay808
[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
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478009
Then we have another loop:

intStart = 1
for intStart = 1 to len(cells(1,1))
    if mid(cells(1,1),intStart,8) like "##:##:##" then
        Range("A1").Characters(intStart, 8).Font.Color = vbRed
        intStart = intStart + 7
    End If
Next

Open in new window

0
 

Author Comment

by:kbay808
ID: 40478047
Is this how it supposed to look?  It's not highlighting any of the dates.  Is the value supposed to be "##:##:##"?  The date has "/".

Sub HighLightText()
Dim intStart As Integer
Dim intEnd As Integer
intStart = 1
Do
    intStart = InStr(intStart, LCase(Range("A3").Value), "wip")
    If intStart > 0 Then
        Range("A3").Characters(intStart, 3).Font.Color = vbBlue
        intStart = intStart + 1
    End If
Loop Until intStart = 0

intStart = 1
For intStart = 1 To Len(Cells(1, 1))
    If Mid(Cells(1, 1), intStart, 8) Like "##:##:##" Then
        Range("A3").Characters(intStart, 8).Font.Color = vbRed
        intStart = intStart + 7
    End If
Next

intStart = 1
Do
    intStart = InStr(intStart, LCase(Range("A3").Value), "PC")
    If intStart > 0 Then
        Range("A3").Characters(intStart, 2).Font.Color = vbRed
        intStart = intStart + 1
    End If
Loop Until intStart = 0

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478050
Yep - change it to ##/##/##

intStart = 1
For intStart = 1 To Len(Cells(1, 1))
    If Mid(Cells(1, 1), intStart, 8) Like "##/##/##" Then
        Range("A3").Characters(intStart, 8).Font.Color = vbRed
        intStart = intStart + 7
    End If
Next
0
Independent Software Vendors: 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:kbay808
ID: 40478079
It did not work.  I even tried it in a new workbook all by it self, but no joy.  

Sub HighLightText()
Dim intStart As Integer
Dim intEnd As Integer
    
intStart = 1
 For intStart = 1 To Len(Cells(1, 1))
     If Mid(Cells(1, 1), intStart, 8) Like "##/##/##" Then
         Range("A3").Characters(intStart, 8).Font.Color = vbRed
         intStart = intStart + 7
     End If
Next
End Sub

Open in new window

0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40478087
Probably because we are both searching cell A1 (lines 6 and 7), but highlighting cell A3 (line 8).

And you don't need lines 3 or 5.

Updated code, using cell A3.

Sub HighLightText()
Dim intStart As Integer
    
 For intStart = 1 To Len(Cells(3, 1))
     If Mid(Cells(3, 1), intStart, 8) Like "##/##/##" Then
         Range("A3").Characters(intStart, 8).Font.Color = vbRed
         intStart = intStart + 7
     End If
Next
End Sub

Open in new window

0
 

Author Comment

by:kbay808
ID: 40478097
The data is in cell A3 and I removed lines 3 and 5.  Still no joy.  I uploaded the example file so you can see what I'm looking at.
Example.xlsm
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40478101
Update the code to the one in my previous post (which is more than removing lines 3 and 5), and it will work.
0
 

Author Closing Comment

by:kbay808
ID: 40478118
It works GREAT!!!   Thank you very much.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

734 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