Excel highlight cell if value exists in another column

I need a formula to highlight all rows in a column if the value exists in another column. So if I have Column A and it has 100 rows 20 of them just have the word cat, 20 dog, 20 bird and 40 lizard. and in Column B I only have 1 row that says cat and one row that says bird I need all the cat and bird rows in column A highlighted. I have been looking for something like this for a while now and can not find it anywhere.

Inhaltsverzeichnis Show

Highlight Cell If Same Value Exists in Another Column
Highlight Cell If Same Value Exists in Another Column Using VBA
Related Functions

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.

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:

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)….