Method 1 – Using Highlight Cells Rules Feature
Steps:
- Select all the cells from B5 to C10.
- Go to the Home tab and click on Conditional Formatting.
- Go to Highlight Cells Rules and click on Duplicate Values.
- Click on OK.
- This should highlight the values that are similar.
Method 2 – Applying New Rule Feature
Steps:
- Select the cells from B5 to C10.
- Navigate to Conditional Formatting under the Home tab and click on New Rule.
- In the new window, select Format only unique or duplicate values and click on Format.
- Select a color under the Fill tab and click OK in this window and the next window.
- This will highlight the values that are similar in the dataset.
Method 3 – Utilizing Equal Operator
Steps:
- Go to cell D5 and insert the following formula:
=B5=C5
- Press Enter and copy this formula to the other cells using Fill Handle.
- This will give TRUE or FALSE values based on whether the values match or not.
Method 4 – Comparing Using EXACT Function
Steps:
- This method, double-click on cell D5 and insert the formula below:
=EXACT(B5,C5)
- Press the Enter key, and consequently, this will insert TRUE if the values are precisely similar.
Method 5 – Using SEARCH Function
Steps:
- Start this method, navigate to cell D5 and type in the following formula:
=IFERROR(IF(SEARCH(C5,B5),"Similar"),"Not Similar")
- Press the Enter key or click on any blank cell.
- This will give you the result as similar or not for all the data.
How Does the Formula Work?
- SEARCH(C5,B5): This portion gives the true value as 1.
- IF(SEARCH(C5,B5),”Similar”): This part gives the result back as Similar.
- IFERROR(IF(SEARCH(C5,B5),”Similar”),”Not Similar”): This also returns the final value as Similar.
Method 6 – Applying VBA Code.
Steps:
- Go to the Developer tab and select Visual Basic.
- Select Insert in the VBA window and click on Module.
- Type in the formula below in the new window:
Sub Highlight()
   Dim xRg1 As Range
   Dim xRg2 As Range
   Dim xTxt As String
   Dim xCell1 As Range
   Dim xCell2 As Range
   Dim I As Long
   Dim J As Integer
   Dim xLen As Integer
   Dim xDiffs As Boolean
   On Error Resume Next
   If ActiveWindow.RangeSelection.Count > 1 Then
     xTxt = ActiveWindow.RangeSelection.AddressLocal
   Else
     xTxt = ActiveSheet.UsedRange.AddressLocal
   End If
lOne:
   Set xRg1 = Application.InputBox("Range A:", "Select Range", xTxt, , , , , 8)
   If xRg1 Is Nothing Then Exit Sub
   If xRg1.Columns.Count > 1 Or xRg1.Areas.Count > 1 Then
       MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Similar or Not"
       GoTo lOne
   End If
lTwo:
   Set xRg2 = Application.InputBox("Range B:", "Select Range", "", , , , , 8)
   If xRg2 Is Nothing Then Exit Sub
   If xRg2.Columns.Count > 1 Or xRg2.Areas.Count > 1 Then
       MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Similar or Not"
       GoTo lTwo
   End If
   If xRg1.CountLarge <> xRg2.CountLarge Then
      MsgBox "Two selected ranges must have the same numbers of cells ", vbInformation, "Similar or Not"
      GoTo lTwo
   End If
   xDiffs = (MsgBox("Click Yes to highlight similarities, click No to highlight differences ", vbYesNo + vbQuestion, "Similar or Not") = vbNo)
   Application.ScreenUpdating = False
   xRg2.Font.ColorIndex = xlAutomatic
   For I = 1 To xRg1.Count
       Set xCell1 = xRg1.Cells(I)
       Set xCell2 = xRg2.Cells(I)
       If xCell1.Value2 = xCell2.Value2 Then
           If Not xDiffs Then xCell2.Font.Color = vbRed
       Else
           xLen = Len(xCell1.Value2)
           For J = 1 To xLen
               If Not xCell1.Characters(J, 1).Text = xCell2.Characters(J, 1).Text Then Exit For
           Next J
           If Not xDiffs Then
               If J <= Len(xCell2.Value2) And J > 1 Then
                   xCell2.Characters(1, J - 1).Font.Color = vbRed
               End If
           Else
               If J <= Len(xCell2.Value2) Then
                   xCell2.Characters(J, Len(xCell2.Value2) - J + 1).Font.Color = vbRed
               End If
           End If
       End If
   Next
   Application.ScreenUpdating = True
End Sub
- Open the macro from the Developer tab by clicking on Macros.
- In the Macro window, select the Highlight macro and click Run.
- Insert the first range in the Select Range window and click OK.
- Select the second range and again click OK.
- Press Yes to confirm.
- The VBA code will highlight a similar value in cell C8.
Download Practice Workbook
<< Go Back To Excel Compare Cells | Compare in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!