How to Combine Two Codes in One Worksheet Change Event in Excel Formula

In Excel, worksheet change events are triggered when a cell or a range of cells on a worksheet is changed by the user or by another VBA code. Worksheet change events can be used to perform various tasks, such as validating data, formatting cells, updating formulas, or sending emails.

However, sometimes we may want to combine two or more codes in one worksheet change event, so that different actions can be taken depending on the location or the value of the changed cells. For example, we may want to convert the text in column A to uppercase, and the text in column B to lowercase, whenever a cell in either column is changed.

How can we do that? In this article, we will explain the basic theory and the procedures of combining two codes in one worksheet change event in excel formula. We will also provide a comprehensive explanation with a detailed example and a real data scenario. Finally, we will discuss some alternative approaches and their advantages and disadvantages.

The basic idea of combining two codes in one worksheet change event is to use the Intersect function and the If statement. The Intersect function returns a range object that represents the intersection of two or more ranges. The If statement executes a block of code if a certain condition is met, and optionally another block of code if the condition is not met.

By using the Intersect function and the If statement, we can check if the changed cells belong to a specific range, and then execute the corresponding code for that range. For example, if we want to convert the text in column A to uppercase, and the text in column B to lowercase, we can use the following pseudocode:

Private Sub Worksheet_Change(ByVal Target As Range)
  ' Check if the changed cells intersect with column A
  If Not Intersect(Target, Range("A:A")) Is Nothing Then
    ' Convert the text in column A to uppercase
  End If
  ' Check if the changed cells intersect with column B
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    ' Convert the text in column B to lowercase
  End If
End Sub

Note that we use the Not operator to reverse the logic of the Intersect function, because the Intersect function returns Nothing if there is no intersection. Also, we use separate If statements for each range, instead of using ElseIf or Else, because the changed cells may intersect with both ranges at the same time.

Procedures

To combine two codes in one worksheet change event in excel formula, we need to follow these steps:

1. Open the Visual Basic Editor (VBE) by pressing Alt + F11 on the keyboard, or by clicking the Developer tab and then the Visual Basic button on the ribbon.
2. In the VBE, select the worksheet that you want to apply the worksheet change event to, by double-clicking its name in the Project Explorer window. This will open the worksheet’s code module in the code window.
3. In the code window, select Worksheet from the left drop-down list, and then select Change from the right drop-down list. This will create a blank Worksheet_Change event procedure in the code window.
4. In the Worksheet_Change event procedure, type the code that you want to combine, using the Intersect function and the If statement as explained in the previous section. For example, if you want to combine the following two codes:

' Code 1: Convert the text in column A to uppercase
Dim Cell As Range
For Each Cell In Target
  If Cell.Column = 1 Then
    Cell.Value = UCase(Cell.Value)
  End If
Next Cell

' Code 2: Convert the text in column B to lowercase
Dim Cell As Range
For Each Cell In Target
  If Cell.Column = 2 Then
    Cell.Value = LCase(Cell.Value)
  End If
Next Cell

You can type the following code in the Worksheet_Change event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
  ' Check if the changed cells intersect with column A
  If Not Intersect(Target, Range("A:A")) Is Nothing Then
    ' Convert the text in column A to uppercase
    Dim Cell As Range
    For Each Cell In Target
      If Cell.Column = 1 Then
        Cell.Value = UCase(Cell.Value)
      End If
    Next Cell
  End If
  ' Check if the changed cells intersect with column B
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    ' Convert the text in column B to lowercase
    Dim Cell As Range
    For Each Cell In Target
      If Cell.Column = 2 Then
        Cell.Value = LCase(Cell.Value)
      End If
    Next Cell
  End If
End Sub

5. Save the workbook and return to Excel. Now, whenever you change a cell in column A or column B, the text in that column will be converted to uppercase or lowercase, respectively.

Explanation

To illustrate how the code works, let us use a detailed example with a real data scenario. Suppose we have a worksheet that contains the names and email addresses of some customers, as shown in the following table:

We want to apply the following rules to the worksheet:

• Whenever we change a cell in column A, the name should be converted to uppercase.
• Whenever we change a cell in column B, the email address should be converted to lowercase.

To achieve this, we can use the code that we wrote in the previous section. Let us see what happens when we change some cells on the worksheet.

• If we change the name in cell A2 from “Alice” to “alice”, the code will check if the changed cell intersects with column A, and then convert the name to uppercase. The result will be “ALICE” in cell A2.
• If we change the email address in cell B3 from “charlie@example.com” to “Charlie@Example.Com”, the code will check if the changed cell intersects with column B, and then convert the email address to lowercase. The result will be “charlie@example.com” in cell B3.
• If we change both the name and the email address in cell A4 and B4 from “David” and “david@example.com” to “dave” and “Dave@Example.Com”, the code will check if the changed cells intersect with both column A and column B, and then convert the name to uppercase and the email address to lowercase. The result will be “DAVE” in cell A4 and “dave@example.com” in cell B4.

As you can see, the code works as expected, and combines two codes in one worksheet change event.

Alternative Approaches

There are some other ways to combine two codes in one worksheet change event, such as using the Select Case statement, the Application.Intersect method, or the Application.WorksheetFunction.CountIf function. However, each approach has its own advantages and disadvantages, and may not work for every situation. Here is a brief comparison of the different approaches:

• The Select Case statement is similar to the If statement, but it allows you to test multiple conditions in a more concise and readable way. For example, instead of using multiple If statements, you can use the following Select Case statement to check the column number of the changed cells:

Select Case Cell.Column
  Case 1 ' Column A
    ' Convert the text to uppercase
  Case 2 ' Column B
    ' Convert the text to lowercase
  Case Else
    ' Do nothing
End Select

The advantage of the Select Case statement is that it can make the code more organized and easier to maintain. The disadvantage is that it may not work well if you need to check more complex conditions, such as the intersection of ranges, the value of cells, or the data type of cells.

• The Application.Intersect method is similar to the Intersect function, but it returns a Variant instead of a Range object. This means that you can use the IsError function to check if there is an intersection, instead of using the Is Nothing operator. For example, instead of using the Intersect function and the If statement, you can use the following code to check the intersection of the changed cells and the ranges:

If Not IsError(Application.Intersect(Target, Range("A:A"))) Then
  ' Convert the text in column A to uppercase
End If
If Not IsError(Application.Intersect(Target, Range("B:B"))) Then
  ' Convert the text in column B to lowercase
End If

The advantage of the Application.Intersect method is that it can avoid some errors that may occur when using the Intersect function, such as the Type Mismatch error or the Object Required error. The disadvantage is that it may not work well if you need to loop through the cells in the intersection, because the Variant type may not support the For Each loop.

• The Application.WorksheetFunction.CountIf function can be used to count the number of cells in a range that meet a certain criterion. For example, instead of using the Intersect function and the If statement, you can use the following code to check if the changed cells are in column A or column B:

If Application.WorksheetFunction.CountIf(Target, "=A*") > 0 Then
  ' Convert the text in column A to uppercase
End If
If Application.WorksheetFunction.CountIf(Target, "=B*") > 0 Then
  ' Convert the text in column B to lowercase
End If

The advantage of the Application.WorksheetFunction.CountIf function is that it can simplify the code and avoid the need to loop through the cells. The disadvantage is that it may not work well if you need to check more specific conditions, such as the value or the data type of the cells.