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!