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.