This post will guide you how to **highlight cell if same value exists in another column** in Excel. How do I highlight cell if value is present in any cell in another column in Excel 2013/2016.

Table of Contents

## Highlight Cell If Same Value Exists in Another Column

Assuming that you have a list of data in range A1:B5, you want to highlight cell in Column A if the value is found in Column B, how to accomplish it. You can use the Conditional Formatting feature to highlight cell in Excel. Just do the following steps:

**Step1:** select your range of cells in which you wish to highlight.

**Step2: **go to **Home** tab, and click **Conditional Formatting** command under **Styles** group, and select **new Rule** from the context menu list. and the **New Formatting Rule** dialog will open.

**Step3:** click **Use a formula to determine which cells to format** option in the Select a Rule Type list, and type the following formula into the **Format values where this formula is true** text box.

**=NOT(ISNA(VLOOKUP(A1,$B:$B,1,FALSE)))**

**Note: **the Cell A1 is the first cell of the column that you want to highlight, and $B:$B is another column that you want to be checked)

**Step4: **click **Format** button in the **New Formatting Rule** dialog box, and the **Format Cell dialog** will open.

**Step5: **switch to **Fill** tab in the **Format Cell** dialog box, and choose one color as you need. click **Ok** button back to the** New Formatting Rule **dialog box.

**Step6: **click OK button. You would see that the cells in Column A have been highlighted if those values can be found in column B.

## Highlight Cell If Same Value Exists in Another Column Using VBA

You can also use an Excel VBA Macro to highlight cell if value is present in another column. Just do the following steps:

**Step1:** open your excel workbook and then click on “**Visual Basic**” command under **DEVELOPER** Tab, or just press “**ALT+F11**” shortcut.

**Step2:** then the “Visual Basic Editor” window will appear.

**Step3:** click “**Insert**” ->”**Module**” to create a new module.

**Step4:** paste the below VBA code into the code window. Then clicking “**Save**” button.

Sub HighlightCellIfValueExistsinAnotherColumn() Dim ws As Worksheet Dim x As Integer Dim Find As Variant Set ws = Worksheets("Sheet4") For x = 1 To ws.Range("A" & Rows.Count).End(xlUp).Row Set Find = ws.Range("B:B").Find(What:=ws.Range("A" & x).Value, LookAt:=xlWhole) If Not Find Is Nothing Then If ws.Cells(Find.Row, 6).Value = 0 And ws.Cells(Find.Row, 9).Value = 0 Then ws.Range("A" & x).Interior.ColorIndex = 6 End If End If Next x End Sub

**Step5:** back to the current worksheet, then run the above excel macro. Click **Run** button.

**Step6:** let’s see the result:

### Related Functions

- Excel VLOOKUP function

The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])…. - Excel ISNA function

The Excel ISNA function used to check if a cell contains the #N/A error, if so, returns TRUE; otherwise, the ISNA function returns FALSE.The syntax of the ISNA function is as below:=ISNA(value)….

## Leave a Reply

You must be logged in to post a comment.