Highlight words that have the same first two characters from two different cells

excelrater

New Member
Joined
May 17, 2019
Messages
21
How do I get excel to Highlight words that have the same first two characters from two different cells

For example
AB
1abfgr( this should highlighted since the same first two letters is found in cell B2)gh( this should highlighted since the same first two letters is found in cell A3)
2bdnm (this should not be highlighted since none had the same first two letters)ab( this should highlighted since the same first two letters is found in cell A1)
3ghfd( this should highlighted since the same first two letters is found in cell B1)cb (this should not be highlighted since none had the same first two letters)

<tbody>
</tbody>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Re: How do I get excel to Highlight words that have the same first two characters from two different cells

The following code should do what you are looking for.

Code:
Sub HFT()
Dim ColA As Object: Set ColA = CreateObject("System.Collections.ArrayList")
Dim ColB As Object: Set ColB = CreateObject("System.Collections.ArrayList")
Dim arA() As Variant: arA = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim arB() As Variant: arB = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Value

For a = LBound(arA) To UBound(arA)
    ColA.Add Left(arA(a, 1), 2)
Next a

For b = LBound(arB) To UBound(arB)
    ColB.Add Left(arB(b, 1), 2)
Next b

For i = 0 To ColA.Count - 1
    If ColB.contains(ColA(i)) Then Cells(i + 1, "A").Interior.ColorIndex = 6
Next i

For j = 0 To ColB.Count - 1
    If ColA.contains(ColB(j)) Then Cells(j + 1, "B").Interior.ColorIndex = 6
Next j

End Sub
 
Upvote 0
Re: How do I get excel to Highlight words that have the same first two characters from two different cells

The following code should do what you are looking for.

Code:
Sub HFT()
Dim ColA As Object: Set ColA = CreateObject("System.Collections.ArrayList")
Dim ColB As Object: Set ColB = CreateObject("System.Collections.ArrayList")
Dim arA() As Variant: arA = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim arB() As Variant: arB = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Value

For a = LBound(arA) To UBound(arA)
    ColA.Add Left(arA(a, 1), 2)
Next a

For b = LBound(arB) To UBound(arB)
    ColB.Add Left(arB(b, 1), 2)
Next b

For i = 0 To ColA.Count - 1
    If ColB.contains(ColA(i)) Then Cells(i + 1, "A").Interior.ColorIndex = 6
Next i

For j = 0 To ColB.Count - 1
    If ColA.contains(ColB(j)) Then Cells(j + 1, "B").Interior.ColorIndex = 6
Next j

End Sub

I appologize I want to do all of column B and all of column J and highlight the cell if the first two letters in B match J is this how the code should look like?

Sub HFT()
Dim ColB As Object: Set ColB = CreateObject("System.Collections.ArrayList")
Dim ColJ As Object: Set ColJ = CreateObject("System.Collections.ArrayList")
Dim arB() As Variant: arB = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Value
Dim arJ() As Variant: arB = Range("J1:J" & Range("J" & Rows.Count).End(xlUp).Row).Value


For a = LBound(arB) To UBound(arB)
ColB.Add Left(arB(a, 1), 2)
Next a


For b = LBound(arJ) To UBound(arJ)
ColJ.Add Left(arJ(b, 1), 2)
Next b


For i = 0 To ColB.Count - 1
If ColJ.contains(ColB(i)) Then Cells(i + 1, "B").Interior.ColorIndex = 6
Next i


For j = 0 To ColJ.Count - 1
If ColB.contains(ColJ(j)) Then Cells(j + 1, "J").Interior.ColorIndex = 6
Next j


End Sub
 
Last edited:
Upvote 0
Re: How do I get excel to Highlight words that have the same first two characters from two different cells

Another option
Code:
Sub excelrater()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Left(Cl.Value, 2)) Then
            .Add Left(Cl.Value, 2), Cl
         Else
            Set .Item(Left(Cl.Value, 2)) = Union(.Item(Left(Cl.Value, 2)), Cl)
         End If
      Next Cl
      For Each Cl In Range("J2", Range("J" & Rows.Count).End(xlUp))
         If .Exists(Left(Cl.Value, 2)) Then
            Cl.Interior.Color = 45678
            .Item(Left(Cl.Value, 2)).Interior.Color = 45678
         End If
      Next Cl
   End With
End Sub
 
Upvote 0
Re: How do I get excel to Highlight words that have the same first two characters from two different cells

Another option
Code:
Sub excelrater()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Left(Cl.Value, 2)) Then
            .Add Left(Cl.Value, 2), Cl
         Else
            Set .Item(Left(Cl.Value, 2)) = Union(.Item(Left(Cl.Value, 2)), Cl)
         End If
      Next Cl
      For Each Cl In Range("J2", Range("J" & Rows.Count).End(xlUp))
         If .Exists(Left(Cl.Value, 2)) Then
            Cl.Interior.Color = 45678
            .Item(Left(Cl.Value, 2)).Interior.Color = 45678
         End If
      Next Cl
   End With
End Sub

thank you for your help How do i implement this code? total newbie here do i click developper then visual basic then click insert module paste the code then click run? what are the proper steps????? thank you for helping me.
 
Upvote 0
Re: How do I get excel to Highlight words that have the same first two characters from two different cells

You can create two conditional format rules:

First Rule:
1. Conditional Formatting, New Rule

2556ab54-9429-4f0c-b257-361bfdc0035a.png


2. Select: Use a formula to dtermine which cells to format
3. In Format values where this formula is true: put this formula
=IF($J1<>"", MATCH(LEFT($J1,2)&"*",B:B,0))
4. Click Format button to choose the color
5. Press Ok
6. Press Ok
7. I applies to: put this:
=$J:$J
8. Press Ok

Second rule:
Repeat the steps and put the following data:
3.
=IF($B1<>"", MATCH(LEFT($B1,2)&"*",J:J,0))
7.
=$B:$B
8. Press OK

When you write data in any B or J column automatically, they will Highlight .
 
Upvote 0
Re: How do I get excel to Highlight words that have the same first two characters from two different cells

You can create two conditional format rules:

First Rule:
1. Conditional Formatting, New Rule

2556ab54-9429-4f0c-b257-361bfdc0035a.png


2. Select: Use a formula to dtermine which cells to format
3. In Format values where this formula is true: put this formula
=IF($J1<>"", MATCH(LEFT($J1,2)&"*",B:B,0))
4. Click Format button to choose the color
5. Press Ok
6. Press Ok
7. I applies to: put this:
=$J:$J
8. Press Ok

Second rule:
Repeat the steps and put the following data:
3.
=IF($B1<>"", MATCH(LEFT($B1,2)&"*",J:J,0))
7.
=$B:$B
8. Press OK

When you write data in any B or J column automatically, they will Highlight .

Thank you for this but i got lost on how to apply #7 .
I applies to: put this:
=$J:$J? where do I apply this?
 
Upvote 0
Re: How do I get excel to Highlight words that have the same first two characters from two different cells

Sorry, my excel is in Spanish, but you have to fill the field "Applies to", look at the image

108396e142b22e8e51a2b3548ede4358.jpg


And press Ok
 
Upvote 0
Re: How do I get excel to Highlight words that have the same first two characters from two different cells

Thank you i will try and report back
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top