Every user can use this method in their worksheets. This method is very easy to understanding. You can refer to it to have a better understanding of those methods. The table below lists all the advantages and disadvantages of the three methods. When you need to check a column in a worksheet, you may have trouble in choosing the methods. Hence, you need to clear the conditional formatting rule in the same range. If there is a rule of conditional formatting in this column, the result of conditional formatting will cover the result of VBA macros. On the other hand, you also need to pay attention to one point. And just by pressing the button on the keyboard, you can get the result immediately. The result is actually the same as the result of conditional formatting. Now come back to the worksheet and check the result.And the result will already appear in the worksheet. You can press the button “F5” on the keyboard to run macro. There are different methods to run macros in Excel. In this step, you will need to run this macro.And we will format the cell with the yellow color. In this macro, you need to change certain elements according to your actual worksheet. If Not Cells(RowReference, 1) + 1 = Cells(RowReference + 1, 1) ThenĬells(RowReference, 1).Interior.Color = vbYellow Now copy the following codes and paste into the module:.And then insert a new module in the editor.Press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.Method 3: VBA MacrosĮxcept for the above two methods, you can also use VBA macros to fulfill this task. Therefore, you can just ignore this cell. This is because there is no value in cell A17. Here the format of the last cell A16 also changes. You will see that if there are missing items, the format of the target cell will change. Next in the “New Formatting Rule” window, continue click “OK” to save the setting of this new rule.When you finish the setting in the “Format Cells” window, click the button “OK” in the window.And here we will change the fill color for the target cell. You can set according to your preference. In this window, you can set a special format for the target cell. And then you will see the “Format Cells” window pop up.Here click the button “Format” in this window. After that, you need to set the format.This formula will also judge whether the second number is the result of the first number plus 1. And then input this formula into the text box:.In the newly pop-up window, choose the last option about using a formula.In the drop-down list, choose the option “New Rule”.And then click the button “Conditional Formatting” in the toolbar.Select the target column in the worksheet. Method 2: Conditional FormattingĮxcept for the IF function, you can also use function with conditional formatting in the worksheet. Come back to the corresponding cells in column A, you will know that the missing items are “20150003”, “20150010” and “20150011”. Thus, you can check according to the result of this formula. There are two cells with the result of “lose data”. And you can see the result in the worksheet. Therefore, you will fill the whole column with the same formula. And then double click the fill handle of this cell.If there is a missing item or there are several missing items, you will see the result “lose data” in the cell. After that, press the button “Enter” on the keyboard.This formula will judge the relationship between the adjacent two cells. And then input this formula into the cell:.You can also select a cell according to your need. In this example, we will click the cell E3 in the worksheet. In this method, you will need to use the IF function for the worksheet. In order to find those missing items quickly and easily, you can use the three methods in the following parts. If you check numbers manually and see the numbers one after another, you will spend a lot of time and energy, especially when there are many items in the column. This means that there are 3 missing items in this worksheet. However, the last employee ID is “20150018”, while the corresponding row is 16. In this worksheet, the first column contains the employee ID. Therefore, it is necessary for you to check the worksheet in your work. However, some reasons will cause some data to be lost, such as copying and pasting, or you forget to collect a sales representative’s information. Usually in your Excel worksheet, there will be a column with consecutive numbers. In this article, we will show you 3 methods to find the missing items in a column with consecutive numbers. When you are working on an Excel file, it is unavoidable to lose some data and information.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |