Highlight cells based on selected cell loop.

[JF123]

Similar to this thread

http://www.mrexcel.com/forum/showthread.php?t=616627

I am looking to highlight values based on a selected cell.

However, would also like the other values in the highlighted cell's row to be used as inputs for the next search and highlight those values as well.

Can anyone help this this macro tweak? Any questions let me know.

Thank you in advance.

 

[Cindy Ellis]

Hello JF123, and Welcome to the forum!
I'm not entirely sure what you're asking, but I'll try to help.
For clarification, if the user selects a cell, you want to look at each value in the same row as the selected cell, then search all cells in the workbook for values that match the ones in that row, and highlight them?
Hoping to helps,

 

[JF123]

Thanks Cindy !!!

Yes, but also include the initial highlighted/selected cell as well.

JF

 

[Cindy Ellis]

Can there be any number of columns in the row with the highlighted cell? Or is there some (hopefully small) number of columns in use

 

[JF123]

<TABLE style="WIDTH: 584pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=776><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" span=8 width=97><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=21 width=97>Column 1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=97>Column 2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=97>Column 3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=97>Column 4</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=97>Column 5</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=97>Column 6</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=97>Column 7</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=97>Column 8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=20>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>11</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>111</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>1111</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=20>2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>22</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>222</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>1111</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>1111</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>8</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=20>3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>33</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>333</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>1111</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>1111</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=20>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>44</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>444</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20>5</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>55</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>555</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=20>6</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>66</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>666</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>1111</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20>7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>77</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>777</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=20>8</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>88</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>888</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>1111</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl63>4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl64>(blank)</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=21>9</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65>99</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65>999</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65>(blank)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65>(blank)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65>(blank)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65>(blank)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl65>(blank)</TD></TR></TBODY></TABLE>


Currently there are 8 Columns only, but many many rows, this is a sample for test I was using, but can't come up with anything.

In this example, I selected 111, from row 1, the remaining should fill in as shown above.

Everything on row 1 is used as input, 1111, is in multiple cells, those values are used as input, (blanks) are ignored.

Hope this helps.

JF

 

[JF123]

I've adapted this code to allow for multiple selections that will highlight matching cells, but takes a long long time with this document.

Hopefully there is a way to use this selected rows input as variables in the program.

JF

===

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static sOldRng As Range
Dim r As Range
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
For Each r In ActiveSheet.UsedRange
If r.Value = Target.Value Then
If sOldRng Is Nothing Then
Set sOldRng = r
Else
Set sOldRng = Union(sOldRng, r)
End If
End If
Next r
With sOldRng
.Font.Color = vbRed
.Interior.Color = vbYellow
End With
End Sub

===

 

[Cindy Ellis]

OK...give this a try. It's modified from the code by Taurean in the link you referenced (which saved a LOT of time )

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'modified from code by taurean
Static sOldRng As Range
Dim ThisRow As Long
Dim r As Range

If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
ThisRow = Target.Row
LastCol = ActiveSheet.Cells(ThisRow, Columns.Count).End(xlToLeft).Column
If Not sOldRng Is Nothing Then
    With sOldRng
        .Font.ColorIndex = xlColorIndexAutomatic
        .Interior.ColorIndex = xlColorIndexNone
    End With
    Set sOldRng = Nothing
End If

With ActiveSheet
For Each r In .UsedRange
    For Each c In .Range(.Cells(ThisRow, 1), .Cells(ThisRow, LastCol))
        If r.Value = c.Value And c.Value <> "" Then
        'If r.Value = Target.Value Then
            If sOldRng Is Nothing Then
            Set sOldRng = r
            Else
            Set sOldRng = Union(sOldRng, r)
            End If
        End If
    Next c
Next r
End With

With sOldRng
    .Font.Color = vbRed
    .Interior.Color = vbYellow
End With

End Sub

This goes in the code for the worksheet...not a "standard" module. Please post back if you're not sure where it belongs.

Hope that helps,

 

[JF123]

Okay, tried that thank you!!! Unfortunately (Blanks) are not excluded, so this might be messing it up.

JF

 

[Cindy Ellis]

Hmmm...it excluded blanks when I tested it. Maybe the "blank" cells aren't truly empty, but have a formula or zero-length string?

 

[Cindy Ellis]

Could it be that values of zero are hidden on the sheet? If so, give this a try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'modified from code by taurean
Static sOldRng As Range
Dim ThisRow As Long
Dim r As Range

If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
ThisRow = Target.Row
LastCol = ActiveSheet.Cells(ThisRow, Columns.Count).End(xlToLeft).Column
If Not sOldRng Is Nothing Then
    With sOldRng
        .Font.ColorIndex = xlColorIndexAutomatic
        .Interior.ColorIndex = xlColorIndexNone
    End With
    Set sOldRng = Nothing
End If

With ActiveSheet
For Each r In .UsedRange
    For Each c In .Range(.Cells(ThisRow, 1), .Cells(ThisRow, LastCol))
        If r.Value = c.Value And c.Value <> "" And r.Value <> 0 Then
        'If r.Value = Target.Value Then
            If sOldRng Is Nothing Then
            Set sOldRng = r
            Else
            Set sOldRng = Union(sOldRng, r)
            End If
        End If
    Next c
Next r
End With

If Not (sOldRng Is Nothing) Then
    With sOldRng
        .Font.Color = vbRed
        .Interior.Color = vbYellow
    End With
End If

End Sub

It leaves the last highlighting when a blank cell is selected...should it do that, or do you want it to use the rest of the values in that row?