How to use case structure for comparing string

Question

So i have a table in excel with different names. I am trying to parse through the table and depending on the name in each cell, change the cell background color. I tried to use a switch control, but its not working for me as some of the names are being skipped. So all I get it, some cells colored correctly and some cells not colored at all. This is the code

Sub selectAll()
    Sheets("Assignments").Activate
    Dim cell As Range
    For Each cell In Range("D7:X48")
        Select Case cell.Text
            ct Case cell.Text
            Case "John H."
                cell.Interior.ColorIndex = 6
            Case "Eve J."
                cell.Interior.ColorIndex = 4
            Case "Sam M."
                cell.Interior.ColorIndex = 46
            Case "Jeremy E."
                cell.Interior.ColorIndex = 46
        End Select
    Next
End Sub

What is `ct Case cell.Text`? Also why `.Text` and not `.Value`? — Siddharth Rout, Oct 03 '14 at 05:43

score 1 · Accepted Answer · answered Oct 03 '14 at 05:49

Why not conditional formatting? If you still want to do it with VBA then read on...

Your code is not working as expected i.e some cells are getting skipped, most probably because there are spaces in the cell or the case (UPPER/LOWER/MIXED) is different.

This is how I would do it. (UNTESTED)

Sub selectAll()
    Dim cell As Range

    With Sheets("Assignments")
        For Each cell In .Range("D7:X48")
            Select Case UCase(Trim(cell.Value))
                Case "JOHN H.": cell.Interior.ColorIndex = 6
                Case "EVE J.": cell.Interior.ColorIndex = 4
                Case "SAM M.", "JEREMEY E.": cell.Interior.ColorIndex = 46
            End Select
        Next
    End With
End Sub

If you want to partially check for a string in the cell then use INSTR with vbTextCompare

score 0 · Answer 2 · answered Oct 03 '14 at 01:54

If some are coloring correctly and some are not, it suggests to me that the some of the cells do not match the text you are looking for. Is there hidden whitespace in the cells that are not being colored?

I would also put in a case else at the end of it with a msgbox or something similar to alert you to when something doesnt match one of your cases. Then you will have a specific instance to investigate. Always important to have a catch all at the end.

VBA doesn't have `Switch/Catch All` – Siddharth Rout Oct 03 '14 at 05:49 — Siddharth Rout, Oct 03 '14 at 05:49

How to use case structure for comparing string

2 Answers2