Option Compare Text

The other method eliminates case sensitivity for all procedures (or macros) in a specified module. This means that if you have 20 macros in a module, each doing something different, running this code removes case-sensitivity from all macros. This means that if we have the word CAT entered in 20 macros doing different things, it will always be seen without regard to case sensitivity.

We do this by placing the words Option Compare Text at the very top of the module we want to make case insensitive. This will ensure that any procedures placed within the same module as the procedure in the following section will no longer be case sensitive.

The code. To insert the code, press Alt/Option-F11, select Insert → Module and paste the following:

Option Compare Text

Sub OptionCompareText( )
Dim rCell As Range

    For Each rCell In Range("A1:A10")
	If 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 OptionCompareText from the list, and click Run.

To make all procedures within the Module case sensitive again we would replace Option Compare Text with Option Compare Binary.