none
Contains IF VBA RRS feed

  • Question

  • Hello,

    I'm a newb to VBA and I need a little help. So I am trying to create a formatting that will search all cells on a worksheet that will highlight the cell with a color. So my key world will trigger the highlight.

    I got it to work only when the cell contains the keyword, but if I add other words into the cell it does not allow the formatting. For example, if my keyword is "CON" and the cell contain CON - Denver Trip, I want it to search that cell for the keyword so it can be highlighted.

    Here is the VBA rule I have created.

    Private Sub Worksheet_Change(ByVal Target As Range)

        Set I = Intersect(Target, Range("B3:AZ551"))
        If Not I Is Nothing Then
            Select Case Target
                Case "CON"
                    ColorIndex = 10
                Case "OCO"
                    ColorIndex = 6
                Case "PS"
                    ColorIndex = 33
                Case "CAMP"
                    ColorIndex = 15
                Case "HBI"
                    ColorIndex = 3
                Case "SD"
                    ColorIndex = 29
                Case "RM"
                    ColorIndex = 44
                Case "UNP"
                    ColorIndex = 2
                Case "OTR"
                    ColorIndex = 22
            End Select

    Target.Interior.ColorIndex = ColorIndex

    End If
    End Sub

     

    Thursday, March 21, 2013 7:14 PM

Answers

  • If your entries need to be case sensitive, use this unchanged (but add other values) - otherwise, change all Target.Value to UCase(Target.Value) which will make the entries case insensitive.

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Range("B3:AZ551")) Is Nothing Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub

        If Target.Value Like "*CON*" Then
            Target.Interior.ColorIndex = 10
            Exit Sub
        End If

        If Target.Value Like "*OCO*" Then
            Target.Interior.ColorIndex = 6
            Exit Sub
        End If

    'Etc.

    End Sub


    Thursday, March 21, 2013 7:38 PM

All replies

  • If your entries need to be case sensitive, use this unchanged (but add other values) - otherwise, change all Target.Value to UCase(Target.Value) which will make the entries case insensitive.

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Range("B3:AZ551")) Is Nothing Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub

        If Target.Value Like "*CON*" Then
            Target.Interior.ColorIndex = 10
            Exit Sub
        End If

        If Target.Value Like "*OCO*" Then
            Target.Interior.ColorIndex = 6
            Exit Sub
        End If

    'Etc.

    End Sub


    Thursday, March 21, 2013 7:38 PM
  • You can do all this with conditional formatting (use Formula option and the SEARCH function). With VBA you need to use the instr function to test for your substrings. SELECT CASE doesn't support that (I think) so you need to convert to If then, ElseIf construct.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Thursday, March 21, 2013 7:42 PM
  • If your entries need to be case sensitive, use this unchanged (but add other values) - otherwise, change all Target.Value to UCase(Target.Value) which will make the entries case insensitive.

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Range("B3:AZ551")) Is Nothing Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub

        If Target.Value Like "*CON*" Then
            Target.Interior.ColorIndex = 10
            Exit Sub
        End If

        If Target.Value Like "*OCO*" Then
            Target.Interior.ColorIndex = 6
            Exit Sub
        End If

    'Etc.

    End Sub



    THANKS BERNIE! It works!
    Thursday, March 21, 2013 7:53 PM
  • You can do all this with conditional formatting (use Formula option and the SEARCH function). With VBA you need to use the instr function to test for your substrings. SELECT CASE doesn't support that (I think) so you need to convert to If then, ElseIf construct.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management


    Rod, I tried using conditional formatting, but it limited me to three.
    Thursday, March 21, 2013 7:54 PM
  • I usually use the structure showed below:

        With Target
            Select Case True
                Case .Value Like "*CON*": .Interior.ColorIndex = 10
                Case .Value Like "*OCO*": .Interior.ColorIndex = 6
                'etc...
            End Select
        End With


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br


    Thursday, March 21, 2013 11:19 PM