Macro that compares strings and then actions

[Tigerexcel]

I receive files that need updating based on whether personnel belong to a particular unit or have a particular characteristic.
Let's say that Tom, Jane and Andy don't need updating of their files but everyone else does. I'd like to create a macro that loops through all the records but leaves Tom, Jane and Andy's records intact. I can create a macro with an if statement that can test a value but I'm not sure how do that with a string. The macro would have to loop through a range of cells and then act if the name does not match a pre-defined list (or array?). I have run into this situation before where a macro that can do what I'm requesting would be useful for other situations so would appreciate any suggestions.

 

[Tigerexcel]

Just to provide some context to this question, say I have the following. I have a list of Staff and I want to bold certain entries. I need a macro that will loop through a range and only pick up cells that are in the list which is contained in Column C

Staff	Staff	List
Tom	Jake	Tom
Jack	Mary	Andy
Sara	Ken
Daniel	Tom
Harry	Fred
Tom	Candy
Andy	Neil

 

[alansidman]

Here is a VBA solution for you.

Option Explicit

Sub StafBold()
    Dim c As Range, rng As Range, i As Long
    Dim lr As Long, lrC As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lrC = Range("C" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2: B" & lr)
    For Each c In rng
        For i = 2 To lrC
            If c = Range("C" & i) Then
                c.Font.Bold = True
            End If
        Next i
    Next c
    MsgBox "completed"
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Tigerexcel]

Thanks Alan for the quick response, the macro works well. If I wanted to run the macro for the entire sheet but I didn't want the list contents themselves to be bolded ie Tom and Andy in C1 and C2, what modification would I have to make?

 

[alansidman]

I am confused by your question. In my example, Column C does not get bolded. Have you changed the code from what I provided? Is your sample not representative of the actual data? Really need to understand what you are doing differently from your example.

 

[Tigerexcel]

Sometimes I receive data all over the place so the cells to be bolded could be in Column C or any other column. I just wondered (so that I have every potential situation covered) whether I could make a small change in the code to potentially pick up cells anywhere on the sheet but leave the criteria cells in that small list alone.

 

[alansidman]

How about an input box that asks you to supply the criteria and you would type it in. Would that be acceptable?

Option Explicit

Sub StafBold()
    Dim c As Range, rng As Range, i As Long
    Dim lr As Long, lrC As Long
    Dim crit As String
    crit = InputBox("What Name to search?")
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lrC = Range("C" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2: B" & lr)
    For Each c In rng
            If c = crit Then
                c.Font.Bold = True
            End If
    Next c
    MsgBox "completed"
End Sub

 

[alansidman]

If the range to search is variable, then this code may be better for you. Still uses the input box to define search criteria.

Option Explicit

Sub StafBold()
    Dim c As Range, rng As Range, i As Long
    Dim lr As Long
    Dim crit As String
    crit = InputBox("What Name to search?")
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2").CurrentRegion
    For Each c In rng
            If c = crit Then
                c.Font.Bold = True
            End If
    Next c
    MsgBox "completed"
End Sub

 

[Tigerexcel]

Thanks Alan, the solution in the last post would be preferable as range is variable however when i try and run this macro it hangs at the inputbox stage and won't let me progress. Also will the inputbox handle multiple cells?

 

[alansidman]

What does this mean?

Also will the input box handle multiple cells?

Perhaps a better explanation of what you want instead of open ended questions. Also don't understand why VBA stalls. Error message. Is your actual file different from your sample data? Need more information. Works in my example based upon your sample provided.