2 Methods to Find and Select Cells with the Same Format in Your Excel Worksheet

Data Recovery, Excel File Recovery, Excel Solutions, File Recovery, Office Solutions, Solutions December 28, 2016

In your Excel worksheet, you will format cells that meet certain criteria. When you need to select cells, you will spend a lot of time. Here we will introduce two effective methods for you to find and select those cells.

Sometimes you need to select cells with the same format and then finish some tasks. For example, you need to change the format or delete the format for those cells. However, in most cases, those cells will be separated in different places. Therefore, you will find it hard to find and select them quickly.

The image below shows such an example. You can see there are several cells with the same format.Example for Select Cells

When you need to select them, you will need to press the button “Ctrl” on the keyboard and use your mouse to select them one by one. This is indeed very time-consuming, especially when there are hundreds or even thousands of target cells. To solve this problem quickly and easily, you can use the following two methods.

Method 1: Find Feature

In this part, we will introduce the first method of using the find feature in Excel.

  1. In the worksheet, press the shortcut keys “Ctrl +F” on the keyboard. Hence, the “Find and Replace” window will pop up.
  2. And then in this window, click the button “Options”.
  3. After that, you will see more options. Here click the “Format” in the window.
  4. And next in the “Find Format” window, choose the format that you need to find.
  5. When you finish the format setting, click “OK”.
  6. Now you will come back to the “Find and Replace” window. Here click “Find All”.Find
  7. Therefore, you will see all the result in this window. In order to select them at the same time, you can press “Ctrl +A” on the keyboard. Thus, all the cells will be selected immediately.Select All

From the above process, you can see that using this method is very simple.

Method 2: Excel VBA

Except for the above method, here you can also use the Excel VBA to finish this task.

  1. Press the button “Alt +F11” on the keyboard to open the Visual Basic editor.
  2. And then click the button “Insert” in the toolbar.
  3. After that, choose the option “Module” in the drop-down menu. Thus, you have inserted a new module in the visual basic editor.Insert Module
  4. After that, copy the following codes into the module:
Sub SelectCellswithSameFormat()
  Dim objRange As Range, TargetCell As Range, i As Integer
  Set TargetCell = Range("B2")
    For Each objRange In ActiveSheet.UsedRange
      If objRange.Interior.Color = Range("B2").Interior.Color Then
        Set TargetCell = Union(TargetCell, objRange)
      End If
    Next objRange
  TargetCell.Select
End Sub

Here we will first find those cells and then select them at the same time. You can also modify the codes according to your actual worksheet.

  1. And then press the button “F5” on the keyboard to run this macro.
  2. After that, come back to the worksheet and see the result.VBA Result

The result is actually the same as the result of using find feature.

A Comparison of the Two Methods

In order for you to better choose from the two methods, here you can refer to the table below:

Comparison

 Find Feature

Excel VBA

Advantages

 1.      This feature is very easy to understand, every user can use it in their worksheet.

2.      By using this feature, you will not meet with errors.

 1.      With just one click, you can get the result immediately.

2.      You can also use this macro in other files. And you only need to change certain elements.

Disadvantages

 1.      Every time you need to find and select cells with the same format, you have to go through all the steps.

2.      Compared with the second method, this method will cost you a lot of time.

 1.      If you are not familiar with Excel VBA, you will meet with errors when modify the codes.

2.      When the worksheet changes, you have to change the complex codes again and again.

Form the above analysis, you must have a deep understanding of the two methods. The next time when you need to select cells with the same format, you can know which method is most suitable for you. Choosing the best methods can save you a lot of time.

Besides, you also need to pay attention to one point. There are times that you will use conditional formatting to format cells. However, those cells with the same format cannot be selected by the above two methods. This is because the format is produced by conditional formatting. Hence, before you use the two methods, you need first figure out whether the format is the result of conditional formatting.

When the Excel Application Fails

When you find the Excel application fails, don’t panic. You need to repair it as soon as possible. Among so many methods, the best choice for you is using a third-party tool. You can use it to repair Excel xlsx data damage and fix other problems. This tool can make sure that you will never lose your precious data and information.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc document and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.