Ucase Function
The Ucase function can compare text in a range of cells, allowing us to write a macro that compares text case-insensitively.
Here is one that displays a message box if it encounters any cell in A1:A10 of the active sheet containing any case variation of the word CAT.
The code.
To insert the code, press Alt/Option-F11, select Insert → Module, and paste the following:
Sub CompareText( )
Dim rCell As Range
For Each rCell In Range("A1:A10")
If UCase(rCell) = "CAT" Then
MsgBox rCell.Address & " has " & rCell & " in it"
End If
Next rCell
End Sub
Exit the VBE and return to Excel and save your workbook.
To run the macro, select Developer → Macros (pre-2007, Tools → Macro → Macros...), select CompareText from the list, and click Run. So if you had the word CAT (uppercase) in cells A1:A9 and the word cat (lowercase) in A10, the macro would display a message box for each instance of the word "cat," regardless of what case it is entered in.
