Highlight only last ones that finds in end of the column

[Kishan]

Using Excel 2000
Hi,

In the cells C3:K3 got 9 patterns with 3 different colour groups I need a VBA which Highlights only 9 patterns finds last in the column C as shown in the example with same format as C3:K3

Example....

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3			1|1	1|X	1|2	X|1	X|X	X|2	2|1	2|X	2|2
4
5
6			1 | 2
7			2 | 2
8			X | 1
9			2 | 1
10			2 | 1
11			1 | 2
12			2 | 1
13			X | X
14			2 | 1
15			1 | 1
16			1 | 2
17			1 | 1
18			X | X
19			1 | 1
20			X | 1
21			1 | X
22			X | 2
23			2 | X
24			1 | 1
25			X | 2
26			2 | 2
27			X | X
28			2 | 1
29			1 | 2
30			1 | X
31			X | 2
32			1 | 2
33			X | 1
34			2 | X
35			X | 1
36			X | X
37			2 | 2
38
39
40
41
Sheet10

Thank you in advance

Regards,
Kishan

 

[Fluff]

You're welcome.

 

[Peter_SSs]

.. even simpler:

Sub Highlight_Last_v2()
  Dim c As Range, cFound As Range
  
  For Each c In Range("C3:K3")
    Set cFound = Range("C6:C37").Find(What:=c.Value, SearchDirection:=xlPrevious)
    If Not cFound Is Nothing Then c.Copy cFound
  Next c
End Sub

.. and if we know that all the values in C3:K3 will exist at least once in C6:C37 then

Sub Highlight_Last_v3()
  Dim c As Range
  
  For Each c In Range("C3:K3")
    c.Copy Range("C6:C37").Find(What:=c.Value, SearchDirection:=xlPrevious)
  Next c
End Sub

 

[Kishan]

.. even simpler:

Sub Highlight_Last_v2()
  Dim c As Range, cFound As Range
  
  For Each c In Range("C3:K3")
    Set cFound = Range("C6:C37").Find(What:=c.Value, SearchDirection:=xlPrevious)
    If Not cFound Is Nothing Then c.Copy cFound
  Next c
End Sub

.. and if we know that all the values in C3:K3 will exist at least once in C6:C37 then

Sub Highlight_Last_v3()
  Dim c As Range
  
  For Each c In Range("C3:K3")
    c.Copy Range("C6:C37").Find(What:=c.Value, SearchDirection:=xlPrevious)
  Next c
End Sub

Peter_SSs, Sub Highlight_Last_v3 is ideal because yes all the values in C3:K3 exist at least once in C6:C37 thank you for shooting the code it is resulting perfect.</SPAN></SPAN>

Can I ask you one more favour? I have add one more column to right side and I want if it could be coloured as shown below in the given example </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3			1 | 1	1 | X	1 | 2	X | 1	X | X	X | 2	2 | 1	2 | X	2 | 2
4
5
6			1 | 2	5
7			2 | 2	0
8			X | 1	2
9			2 | 1	9
10			2 | 1	9
11			1 | 2	5
12			2 | 1	9
13			X | X	1
14			2 | 1	9
15			1 | 1	13
16			1 | 2	5
17			1 | 1	13
18			X | X	1
19			1 | 1	13
20			X | 1	2
21			1 | X	7
22			X | 2	6
23			2 | X	3
24			1 | 1	13
25			X | 2	6
26			2 | 2	0
27			X | X	1
28			2 | 1	9
29			1 | 2	5
30			1 | X	7
31			X | 2	6
32			1 | 2	5
33			X | 1	2
34			2 | X	3
35			X | 1	2
36			X | X	1
37			2 | 2	0
38
39
40
41
Sheet10-1

Kind Regards</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Peter_SSs]

I have add one more column to right side and I want if it could be coloured as shown below in the given example

Try

Sub Highlight_Last_v4()
  Dim c As Range, cFound As Range
  
  For Each c In Range("C3:K3")
    Set cFound = Range("C6:C37").Find(What:=c.Value, SearchDirection:=xlPrevious)
    c.Copy cFound
    cFound.Offset(, 1).Interior.Color = vbGreen
  Next c
End Sub

 

[salim hasan]

What about this code?

Option Explicit


Sub Colorize()
Dim r%, My_color%, Mn%, Mx%
Dim x%, y%
Mn = 3: Mx = 11
r = Range("C6").End(4).Row
Range("C6:D" & r).Interior.ColorIndex = xlNone
For x = Mn To Mx
 
 My_color = Cells(2, x).Interior.ColorIndex
    For y = r To 6 Step -1
       If Cells(y, 3) = Cells(2, x) Then
            With Cells(y, 3)
                .Interior.ColorIndex = My_color
                .Offset(, 1).Interior.Color = vbGreen
            End With
          Exit For
       End If
    Next y
Next x


End Sub

 

[Kishan]

Try

Sub Highlight_Last_v4()
  Dim c As Range, cFound As Range
  
  For Each c In Range("C3:K3")
    Set cFound = Range("C6:C37").Find(What:=c.Value, SearchDirection:=xlPrevious)
    c.Copy cFound
    cFound.Offset(, 1).Interior.Color = vbGreen
  Next c
End Sub

Peter_SSs, superb! Worked as request. Thank you so much</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[Kishan]

What about this code?

Option Explicit

Sub Colorize()
  My_color = Cells(2, x).Interior.ColorIndex
    For y = r To 6 Step -1
       If Cells(y, 3) = Cells(2, x) Then
End Sub

salim hasan, thank you it worked but Peter_SSs, code is easy to manage...also I change 2 to 3 following to get work your code with my data layout.</SPAN></SPAN>

My_color = Cells(3, x).Interior.ColorIndex
    For y = r To 6 Step -1
       If Cells(y, 3) = Cells(3, x) Then

Kind Regards</SPAN></SPAN>
Kishan</SPAN></SPAN>

 

[Peter_SSs]

Peter_SSs, superb! Worked as request. Thank you so much</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Kishan </SPAN></SPAN>

You're welcome.

..also I change 2 to 3 following to get work your code with my data layout.

You would have also needed to include the change to white font in that code.

 

[Kishan]

You're welcome.



You would have also needed to include the change to white font in that code.

Peter_SSs, yes I did not bother to change the font colours, as I am using your code it is vary simple and flexible while changing the locations is easy and so practical </SPAN></SPAN>

Thank you for the nice code </SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Kishan </SPAN></SPAN>