Highlights only last rows

[motilulla]

Hello,

I am looking if someone can make VBA that can highlights last rows in the column C:H as per data last data row column K:Q as per example below

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4	N	Year	n1	n2	n3	n4	n5	n6	n7	EM1	N1	N2	N3	N4	N5	N6	N7
5	N	Year	n1	n2	n3	n4	n5	n6	n7	EM1	N1	N2	N3	N4	N5	N6	N7
6	1	70/71	X	1	1	2	1	2	1		1	3	2	2	5	4	1
7	2	70/71	2	1	X	X	2	2	2		5	1	4	2	2	1	4
8	3	70/71	2	1	2	1	1	2	2		1	1	3	5	2	1	1
9	4	70/71	1	X	X	1	1	1	X		5	5	2	1	1	4	5
10	5	70/71	2	1	2	X	X	1	X		2	2	2	3	6	1	1
11	6	70/71	2	2	2	1	1	1	X		1	6	1	2	2	1	1
12	7	70/71	1	1	1	X	2	2	2		3	2	6	2	5	4	4
13	8	70/71	1	X	1	1	2	1	2		1	4	1	2	1	2	1
14	9	70/71	1	1	2	1	1	X	1		1	2	3	1	3	10	8
15	10	70/71	2	2	1	X	1	2	2		4	4	2	3	1	3	2
16	11	70/71	2	X	X	X	2	1	2		1	3	7	1	3	3	1
17	12	70/71	2	1	X	2	2	2	2		1	3	1	11	1	2	1
18	13	70/71	1	2	X	X	2	1	2		4	3	1	2	1	2	1
19	14	70/71	X	X	1	1	2	1	1		13	3	4	5	1	1	5
20	15	70/71	1	1	2	2	1	1	2		2	3	6	3	5	1	2
21	16	70/71	X	2	1	1	2	2	1		2	3	2	2	2	4	2
22	17	70/71	1	1	2	X	1	1	X		2	2	2	4	2	2	11
23	18	70/71	1	2	X	1	1	2	2		1	2	5	2	1	2	3
24	19	70/71	2	1	2	1	2	1	1		7	2	2	1	3	2	3
25	20	70/71	1	1	2	X	X	1	1		2	1	1	3	15	1	1
26	21	70/71	2	1	1	X	2	1	2		2	1	5	1	2	1	3
27	22	70/71	1	X	2	1	2	1	1		2	8	2	3	1	1	2
28	23	70/71	1	2	X	X	2	2	2		1	5	5	2	1	5	2
29	24	70/71	X	1	X	2	1	1	2		8	3	1	9	6	2	1
30	25	70/71	X	1	1	1	1	2	1		1	1	4	3	1	2	3
31	26	70/71	1	2	2	2	2	2	X		3	3	4	2	3	1	9
32
33
Sheet10

Thank you all
Excel 2000
Regards,
Moti

 

[motilulla]

This should only highlight rows 6 and down

Thank you Scott T, it is running fine as per requested Post#5.

Have a good day

Regards,
Moti

 

[motilulla]

Thank you MickG, What if I ask can you make it could be highlighted in 2 alternate colours like cyan and green (I mean to say row 11 in cyan row 20 in green row 22 in cyan may be)
Regards,
Moti

MickG, after applying the code with my full data I think it is fine as you supplied the code, alternative colours will be the disturbing sorry for the hassle.

Thank you, have a nice day

Good luck!

Regards,
Moti

 

[MickG]

You're welcome
NB:- If you require colours Cyan, Green, Cyan then use code below:-

[COLOR=navy]Sub[/COLOR] MG02Nov17
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R [COLOR=navy]As[/COLOR] Range, col [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("B6", Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Dn.Offset(, 17).Value = 6 [COLOR=navy]Then[/COLOR]
    col = IIf(col = vbCyan, vbGreen, vbCyan)
    [COLOR=navy]For[/COLOR] ac = 1 To 7
        [COLOR=navy]Set[/COLOR] R = Dn.Offset(, ac + 8)
        R.Interior.Color = col
        Dn.Offset(-R + 1, ac).Resize(R).Interior.Color = col
    [COLOR=navy]Next[/COLOR] ac
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[motilulla]

You're welcome
NB:- If you require colours Cyan, Green, Cyan then use code below:-

[COLOR=navy]Sub[/COLOR] MG02Nov17
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] R [COLOR=navy]As[/COLOR] Range, col [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("B6", Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Dn.Offset(, 17).Value = 6 [COLOR=navy]Then[/COLOR]
    col = IIf(col = vbCyan, vbGreen, vbCyan)
    [COLOR=navy]For[/COLOR] ac = 1 To 7
        [COLOR=navy]Set[/COLOR] R = Dn.Offset(, ac + 8)
        R.Interior.Color = col
        Dn.Offset(-R + 1, ac).Resize(R).Interior.Color = col
    [COLOR=navy]Next[/COLOR] ac
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

MickG, much appreciates for coding alternate colours code it is fine.

Have a nice day

Good luck!

Regards,
Moti

 

[MickG]

You're welcome