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

[excelrater]

How do I get excel to Highlight words that have the same first two characters from two different cells

For example [TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]abfgr( this should highlighted since the same first two letters is found in cell B2)[/TD]
[TD]gh( this should highlighted since the same first two letters is found in cell A3)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]bdnm (this should not be highlighted since none had the same first two letters)[/TD]
[TD]ab( this should highlighted since the same first two letters is found in cell A1)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ghfd( this should highlighted since the same first two letters is found in cell B1)[/TD]
[TD]cb (this should not be highlighted since none had the same first two letters)[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

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.

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

 

[excelrater]

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.

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

 

[Fluff]

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

Another option

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

 

[excelrater]

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

Another option

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.

 

[DanteAmor]

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

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 .

 

[excelrater]

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

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?

 

[DanteAmor]

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

And press Ok

 

[excelrater]

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

 

[excelrater]

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

And press Ok

It Worked thank you so much.