Matching cells at specific intervals and color filling

[Usercode]

I have a table where cells have comma separated different numbers. I put 3 columns (A,B,C) and 8 rows. The actual table is larger.

matchproblem — imgbb.com

I am looking to find such a function; starting from the top of each column, if a value(s) in a cell matches with a value(s) in the cell down 4 rows in the same column, this cell (A1) is set to green color.


For example, in column A; if a number in A1 matches with a number in A5, A1 is set to green color. On this table, 90 in A1 and 90 in A5 is a match, so A1 is green color. Likewise, If a number in A2 matches with A6, A2 should set to green color automatically.


If there is no match, there should be no color change. The same goes for A3 and A7, A4 and A8, B1 and B5, B2 and B6, B3 and B7 etc.. down to the last cell with data in every column. I would really appreciate any help. Please let me know if anything is unclear. Thanks.

 

[Usercode]

Here is a sample data file I prepared. It is not the same what image shows though.

Sample data: https://1drv.ms/x/s!AoGkZUHlKui9gQtt83xXMUHykRpN

 

[NoSparks]

Hello Usercode / JJ3

Spent some time on this prior to discovering it's a cross posted question.
Be sure you carefully check the results of the solution you've accepted.

 

[Rick Rothstein]

You should really post a link to any other forums you post your question in. Besides being in the forum rules you agreed to when you joined this forum (and I'll bet the forum rules for any of the other forums you are a member of), it is a common courtesy to the volunteers you are asking to help you. I don't know what other forum you posted to, but I would be disappointed if I have ended up wasting my time developing a solution for you that someone else might have already posted hours ago on some other forum... and the same applies for those volunteers on the other forum(s) you posted your question to. If you posted a link to the other forum(s), I could check that out before spending the time developing a solution for you.

Anyway, with that said, here is what I came up with... I am pretty sure it does what you asked for.

[table="width: 500"]
[tr]
	[td]Sub CheckSingleNumberInCellFourDown()
  Dim R As Long, C As Long, LastRow As Long, LastCol As Long, V As Variant
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  For C = 1 To LastCol
    LastRow = Cells(Rows.Count, C).End(xlUp).Row
    For R = 1 To LastRow - 3
      For Each V In Split(Cells(R, C).Value, ",")
        If InStr("," & Cells(R + 4, C).Value & ",", "," & V & ",") Then Cells(R, C).Interior.Color = vbGreen
      Next
    Next
  Next
End Sub[/td]
[/tr]
[/table]

 

[Usercode]

Thanks for your advice. I will be more careful next time. yes I got an answer for this yesterday night, and I thought of closing this thread here, but I wanted to see another approach actually. Thanks Rick, I will try your code now.

 

[Rick Rothstein]

Thanks for your advice. I will be more careful next time. yes I got an answer for this yesterday night, and I thought of closing this thread here, but I wanted to see another approach actually. Thanks Rick, I will try your code now.

Why don't you post a link to your other thread so that we can see what others posted as solutions.

 

[Usercode]

The answer I got on the other forum is here. Here is the link. Matching cells at specific intervals and color filling
thanks.

 

[Usercode]

I comment the forum link to the codes so someone else wants to use it and want to see details or related discussions, they can go to the forum and read it too.