Use of wild cards

[Livin404]

Hello, I havea basic macro and it does work; however, I need to use a wilde card "*" and it doesn't work. After DEP and ARR I need a wild card. In Column one it will always begin with a DEP or ARR. Lastly I would like those colors to extend through Column F.

My macro is:

Sub Local_BACKGROUND()
    Sheets("Schedule").Select
    endrow = Range("A" & Rows.count).End(xlUp).Row
    
    For Each cell In Range("A1:A" & endrow)
        Select Case cell.Value
            Case "DEP"
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "A")).Interior.ColorIndex = 35
            Case "ARR"
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "A")).Interior.ColorIndex = 22
            End Select
    Next cell
End Sub

 

[balki]

Hi Livin, since all cells will always begin with DEP or ARR, then you could change the cell.value to only look at the first three characters using the LEFT function.

Select Case Left(cell.Value, 3)

 

[Oaktree]

If you were to have differing lengths of strings, another way to do this is:

Select Case True
            cell.value like "DEP*"
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "A")).Interior.ColorIndex = 35
            cell.value like "ARR*"
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "A")).Interior.ColorIndex = 22
End Select

 

[Livin404]

Hi Livin, since all cells will always begin with DEP or ARR, then you could change the cell.value to only look at the first three characters using the LEFT function.

Select Case Left(cell.Value, 3)

Thank you so much, That part worked, how can I get the cell fill to go through Column F.?

 

[Joe4]

Thank you so much, That part worked, how can I get the cell fill to go through Column F.?

You currently have it going from column A to column A:

Range(Cells(cell.Row, "A"), Cells(cell.Row, "A")).Interior.ColorIndex = 35

Just update it to go from A to F:

Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex = 35

 

[Livin404]

You currently have it going from column A to column A:

Range(Cells(cell.Row, "A"), Cells(cell.Row, "A")).Interior.ColorIndex = 35

Just update it to go from A to F:

Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex = 35

Spot on- thank you!

 

[Joe4]

You are welcome.