What I would like additionally, is for the macro to compare say A-1 with A-2, then make both cells color as red text. X.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNoįor example in sheet1, C-3 and C-3 occurs twice, so the macro will get rid of one occurrence of C-3. Subscribe to get more articles like this oneĭid you find this article helpful? If you would like to receive new articles, join our email list.Lstrw = sh.Range('A' & Rows.Count).End(xlUp).Row You can use all kinds of other formulas in rules to format cells. These are just a few examples of conditional formatting with formulas. Excel will then copy the formula down and adjust for the remaining cells. Enter the formula for the range of cells using absolute referencing (since this range should not change) and for the cell holding the criteria using relative referencing and refer to the active cell. In the formula box, enter =COUNTIF(absolute range,cell containing criteria)>1. Click Use a formula to determine which cells to format.Select the range of cells in the column where some of the cells contain duplicates.To apply conditional formatting to highlight the duplicates: In the example below, there are duplicates in column A: You can also use conditional formatting to highlight duplicates in a range of cells. Highlighting duplicates using conditional formatting Excel uses relative referencing by default so the formula will be copied down and adjusted for the remaining cells.

Enter the formula for the first cell in the range.

In the formula box, enter =ISBLANK(active cell reference). Select the range of cells in the column where some of the cells contain blanks.To highlight the blanks using conditional formatting: In the example below, there are blanks in column C: You can use conditional formatting to highlight blanks in a range of cells. Highlighting blanks using conditional formatting Click the Fill tab and select a light fill.In the formula box, enter =ISERROR(active cell reference). Select New Rule from the drop-down menu.Click the Home tab in the Ribbon and select Conditional Formatting in the Styles group.Select the range of cells in a column where some of the cells contain errors.To highlight the errors using conditional formatting: In example below, there are errors in column C: You can use conditional formatting to highlight errors in a range of cells. Recommended article: 15 Microsoft Excel Keyboard Shortcuts to Speed Up Formattingĭo you want to learn more about Excel? Check out our virtual classroom or live classroom Excel courses > Highlighting errors using conditional formatting Below is a sample of a rule with a formula: You can apply conditional formatting using a formula and a new rule. In this article, we'll use some simple examples to show the power of conditional formatting.Ĭonditional Formatting appears on the Home tab in the Ribbon in the Styles group. Use Conditional Formatting in Excel with Formulas to Highlight Errors, Blanks and Duplicatesīy Avantix Learning Team | Updated April 7, 2021Īpplies to: Microsoft ® Excel ® 2010, 2013, 2016, 2019 and 365 (Windows)Ĭonditional formatting is a great tool in Excel and if you combine it with formulas, you can do some amazing things.

Excel highlight duplicates