Adding to "If" VBA

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Hi,
I would like to add several possibilities of words to the statement below. Right now it's considering only "quiz", I would like to add other words such as "unit", "assessment", "test". Right now I have to manually update this each time I run the VBA. I experimented adding it but I am not getting it right. Any help would be appreciated.


Sub Dannottheman3()

Dim Cl As Range

For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))

If LCase(Cl.Value) Like "*quiz*" Then

Cl.Interior.Color = rgbLightBlue

Cl.Font.Bold = True

End If

Next Cl

End Sub
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

eduzs

Well-known Member
Joined
Jul 6, 2014
Messages
639
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe an array?
VBA Code:
Dim aStr, x As Integer
aStr = Array("x", "y", "z")
For x = 0 To UBound(aStr)
    If LCase(Cl.Value) Like "*" & aStr(x) & "*" Then
        ....
    End If
Next x
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
310
Office Version
  1. 2016
Platform
  1. Windows
Oh, I'm late. Anyway, this is how I have changed your macro:
VBA Code:
Option Explicit
Sub Dannottheman3()
    Dim Cl     As Range
    Dim srch As Variant
    For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
        For Each srch In Array("*quiz*", "*unit", "*assessment*", "test") '<= note the different use of the jolly (could be useful)
            If LCase(Cl.Value) Like srch Then
                Cl.Interior.Color = rgbLightBlue
                Cl.Font.Bold = True
                Exit For
            End If
        Next srch
    Next Cl
End Sub
 
Last edited:
Solution

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Oh, I'm late. Anyway, this is how I have changed your macro:
VBA Code:
Option Explicit
Sub Dannottheman3()
    Dim Cl     As Range
    Dim srch As Variant
    For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
        For Each srch In Array("*quiz*", "*unit", "*assessment*", "test") '<= note the different use of the jolly (could be useful)
            If LCase(Cl.Value) Like srch Then
                Cl.Interior.Color = rgbLightBlue
                Cl.Font.Bold = True
                Exit For
            End If
        Next srch
    Next Cl
End Sub
Works beautifully! You have made my task MUCH easier! Thank you!!!
 

Dannottheman

Board Regular
Joined
Dec 24, 2020
Messages
55
Office Version
  1. 2007
Maybe an array?
VBA Code:
Dim aStr, x As Integer
aStr = Array("x", "y", "z")
For x = 0 To UBound(aStr)
    If LCase(Cl.Value) Like "*" & aStr(x) & "*" Then
        ....
    End If
Next x
Thank you for this. :)
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
310
Office Version
  1. 2016
Platform
  1. Windows
Glad having been of some help (y) .
 

Watch MrExcel Video

Forum statistics

Threads
1,118,523
Messages
5,572,639
Members
412,478
Latest member
MakeItWorkVBA
Top