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