Highlight both row if there is a match from column C in column A

[LBee]

I might be on an impossible mission - I have no idea how to solve my problem, and on top of that I'm also having a hard time trying to explain what I want

What im trying to accomplish is looking in column C - if value has more than 2 digits, and is present any part of column A, I want to highlight both rows.
If we take it row-by-row, there is no match in C1 (we wont look at it beacuse it only contains 1 digit), same goes for row 2 and 3.
C4 has a match in A1, and row 1 and 4 will be highlighted. C5 has a match in A2 and row 2 and 5 will be highlighted.
C6 has no match, and will stay white.

The end goal would be that row 1,2,4,5,7,8,11 and 13 would be highlighted.

Is the above possible? (and does my description make sense at all?)

Book1
	A	B	C	D
1	1-123-1		1
2	1-234-1		2
3	1-345-1		3
4	1-456-1		123
5	1-567-1		234
6	1-678-1		555
7	1-789-1		910
8	1-890-1		333
9	1-910-1		445
10	1-101-1		554
11	111-1		333
12	1-222		713
13	333		888
14
Sheet1

 

[smozgur]

Perhaps it could also be solved by using a function and conditional formatting, I don't know. But VBA can do that.
Sample below. Note: I think you missed row 9, it matches with C7.

Sub doIt()
Dim rng As Range
Dim trg As Range
Dim cll As Range
Dim cll2 As Range
    
    Set trg = Range("A1:A13")
    Set rng = trg.Offset(, 2)
    
    For Each cll In rng.Cells
        If Len(cll) > 1 Then
            For Each cll2 In trg.Cells
                If InStr(cll2, cll) Then
                    cll2.Font.Color = vbRed
                    cll.Offset(, -2).Font.Color = vbRed
                End If
            Next cll2
        End If
    Next cll
End Sub

 

[Snakehips]

If perchance you are not able to utilise a vba solution such as proposed by @smozgur then maybe the below might provide food for thought?

Only you know exactly what you have and the reason why you wish to highlight rows as described.
Hence, the below comments / queries.

Lack of response on this is indicative of it not being simple to solve by standard means.
The below is reliant upon a 'helper' column, E that forms the basis for Conditional formatting formulas.
The helper column could be any other out of the way column and could be hidden.
Can you tolerate using a 'helper column'?

The below selectively highlights only relevant cells in C and A. It could easily be modified to highlight each ROW of a match.
However, might it be a tad confusing to highlight the row for both C and A as only one of the AC diagonals would be your match?

Either way, nothing other than scrutiny will easily distinguish true matches.

Maybe that is where a visible 'Helper' column E might be an advantage as it shows the row in which C can be found in A ?

Not sure if you will have duplicates?
Below is ok if you have duplicates in C. It will match all but only to the first 'match' in A.

Book3
	A	B	C	D	E
1	1-123-1		1
2	1-234-1		2
3	1-345-1		3
4	1-456-1		123		1
5	1-567-1		234		2
6	1-678-1		555
7	1-789-1		910		9
8	1-890-1		333		13
9	1-910-1		445
10	1-101-1		554
11	111-1		333		13
12	1-222		713
13	333		888
14
15
16
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
E1:E20	E1	=IFERROR(IF(LEN(C1)>2,MATCH("*"&"-"&C1&"-"&"*","-"&A$1:A$20&"-",0),""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A20	Expression	=MATCH(ROW(A1),E$1:E$20,0)>0	text	NO
C1:C20	Expression	=ISNUMBER(E1)	text	NO

As I say, maybe food for thought?

 

[LBee]

I can confirm that both solutions are working.
Thank you both, I really appreciate it

I'm neither particularly experienced in excel nor this forum, can I mark both answers as solutions?
I like both solutions, they each have their strengths - it's hard for me to say which is the best

 

[smozgur]

I can confirm that both solutions are working.
Thank you both, I really appreciate it

I'm neither particularly experienced in excel nor this forum, can I mark both answers as solutions?
I like both solutions, they each have their strengths - it's hard for me to say which is the best

You're welcome.

You can mark only one post as the solution in a t thread, however, you can also up-vote by using the heart icon for each solution as well if multiple posts helped you.

In this thread, I would certainly mark @Snakehips' post as the solution since it is a better implementation with formula and conditional formatting.

 

[kevin9999]

I had a slightly different interpretation of what you were asking for in your original post. Where you said:

and is present any part of column A, I want to highlight both rows.

I thought you meant you wanted the corresponding cell in A or C also colored in. I had already started coding a solution in that regard, so here is what I came up with FWIW.

Sub Both_Columns()
    Dim rng As Range, c As Range, a As Range, s As String
    Set rng = Range("A1:A13")
    s = WorksheetFunction.TextJoin(" | ", True, rng)
    
    For Each c In rng.Offset(, 2)
        If Len(c) > 1 And InStr(s, c) Then
            Union(c, c.Offset(, -2)).Interior.Color = RGB(248, 203, 173)
            For Each a In rng
                If InStr(a, c) Then Union(a, a.Offset(, 2)).Interior.Color = RGB(248, 203, 173)
            Next a
        End If
    Next c
End Sub

If the solution in post #3 is what you were actually after, then here's a variation of post #2 that fills the interior rather than color the font:

Sub One_Column()
    Dim rng As Range, c As Range, a As Range, s As String
    Set rng = Range("A1:A13")
    s = WorksheetFunction.TextJoin(" | ", True, rng)
    
    For Each c In rng.Offset(, 2)
        If Len(c) > 1 And InStr(s, c) Then
            c.Interior.Color = RGB(248, 203, 173)
            For Each a In rng
                If InStr(a, c) Then a.Interior.Color = RGB(248, 203, 173)
            Next a
        End If
    Next c
End Sub

Either way, I agree with @smozgur that @Snakehips solution is the best one, because if you can get away without using VBA for your solution, then you're probably better off

 

[LBee]

First of all thanks to all of you for helping me with this

I went for @Snakehips solution, as mentioned by @kevin9999 and @smozgur I does work without vba (not an issue here, but could have been), but also the helper-column, which I didn’t think of, but is really helpful.

Now I’m trying to understand what I’m doing



I tried to break down the formula to make it easier to understand =IFERROR(IF(LEN(C1)>2,MATCH("*"&"-"&C1&"-"&"*","-"&A$1:A$20&"-",0),""),"")



IFERROR was new to me, but when reading the documentation, I guess its to avoid the #N/A if the IF statement generates an error

The IF and LEN also make sense, but I find the MATCH function a little more tricky (actually much more)


According to the documentation the syntax of the function is MATCH(lookup_value, lookup_array, [match_type]) – when trying to split it up, it should be like this


lookup_value = "*"&"-"&C1&"-"&"*"

lookup_array = "-"&A$1:A$20&"-"

match_type = 0



I guess the lookup value is "anything" and a "-" and the value of C1 and a "-" and "anything"?

The match_type is 0 because is has to be exact match, but the lookup_array i really can't wrap my head around.

First I thought it was looking what inside "-" and "-", but then it shouldn't work in row 13.

Could someone try to explain what It does?



And I'm also struggling with formatting rule

My goal was to mark the entire row if there was a match and the "=ISNUMBER(E1)" did I solve by changing it to "=ISNUMBER($E1)" (not sure this is the correct way to do it, but it seems to be working), but I cant figure out how fix the condition to mark the entire row in the other condition?

 

[Snakehips]

@LBee Great that we have been able to help!

Below shows revised CF formulas to CF the whole row of the 'Applies To' range, (Edit range to suit)

Book3
	A	B	C	D	E
1	1-123-1		1
2	1-234-1		2
3	1-345-1		3
4	1-456-1		123		1
5	1-567-1		234		2
6	1-678-1		555
7	1-789-1		910		9
8	1-890-1		333		13
9	1-910-1		445
10	1-101-1		554
11	111-1		333		13
12	1-222		713
13	333		888
14
15
16
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
E1:E20	E1	=IFERROR(IF(LEN(C1)>2,MATCH("*"&"-"&C1&"-"&"*","-"&A$1:A$20&"-",0),""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E20	Expression	=MATCH(ROW(A1),$E$1:$E$20,0)>0	text	NO
A1:E20	Expression	=ISNUMBER($E1)	text	NO

I will post back shortly to answer your other queries.

 

[kevin9999]

Could someone try to explain what It does?

I'll give it a go

IFERROR was new to me, but when reading the documentation, I guess its to avoid the #N/A if the IF statement generates an error

In fact, it returns something other than any error message (#N/A!, #REF!, #VALUE! etc.) if the formula returns any error - it's whatever you put after the last comma. In this case you're telling it to return a blank: ,"")

lookup_value = "*"&"-"&C1&"-"&"*"

I guess the lookup value is "anything" and a "-" and the value of C1 and a "-" and "anything"?

Correct. In this case, the asterisk (*) was used, which represents any number of characters. If you wanted to specify an exact number of characters, you would use the question mark instead (?) which represents a single character. So if you wanted to represent 3 characters, you would use ???.

lookup_array = "-"&A$1:A$20&"-"

You have to look at this in the context of the lookup value. The first part of @Snakehips formula, the lookup value is constructed as:

"*"&"-"&C1&"-"&"*"

so the value in cell C1 is having a wildcard representing any number of characters (*) followed by a dash (-) then the cell value, followed by another dash and another wildcard before that value is looked for in the lookup array. The lookup array, is having a dash (-) both prefixed and suffixed to it before it is used. Therefore, the "333" in row 13 is turned into -333- before the Match function looks for it in an array (range) that has been converted to a -[cell value]- (note the dashes) format before it evaluates.

And I'm also struggling with formatting rule

did I solve by changing it to "=ISNUMBER($E1)

Yes, (well done!) but read on...

but I cant figure out how fix the condition to mark the entire row in the other condition

You also need to change the range of cells that the CF formula applies to. In post #3 it's being applied to cells C1:C20. If you wanted to format columns A and B and C (or beyond) with that rule, then you change the range it applies to by selecting (e.g.) cell C1, select Conditional Formatting, select Manage Rules and highlight the box on the right that currently shows $C$1:$C$20 - then on your sheet highlight all the cells in range A1:C20, select Apply, then OK.

 

[Snakehips]

Thanks @kevin9999 for a comprehensive response re the formula. I can go go get me a coffee now, instead!

It may well be overkill but, I will just explain why I chose to use the "-" prefix and suffix, inside of the * wildcards.
Without the "-"s it would be possible for eg 999 in C to match with 1-9992-1 in A

Book3
	A	B	C	D	E	F
11	111-1		333		13
12	1-222		713
13	333		888
14	1-222		999		15	<<Error!!???
15	1-9999-1		873			<<Error!!???
16
17
Sheet1

Cell Formulas
Range	Formula
E11:E17	E11	=IFERROR(IF(LEN(C11)>2,MATCH("*"&C11&"*","-"&A$1:A$20&"-",0),""),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E1,A16:D20,B14:D15,A14,A2:D13,E2:E20	Expression	=MATCH(ROW(A1),$E$1:$E$20,0)>0	text	NO
A1:E1,A16:D20,B14:D15,A14,A2:D13,E2:E20	Expression	=ISNUMBER($E1)	text	NO
A15	Expression	=MATCH(ROW(A15),$E$1:$E$20,0)>0	text	NO
A15	Expression	=ISNUMBER($E14)	text	NO

In my mind that is an error.
However, -999- will not match with -1-9992-1-
Hope that helps.