Highlight 3 diagonals

[Kishan]

Using Excel 2000

Hi,

I need to highlights 3 diagonals weather are they numbers or alphabets

In the example below check first column C to find diagonal in the column D & E

For example C6 is empty go for C7 if C7 is empty go for C8, Yes find diagonal in C8, D9 & in E10 (in this case as in the row 8 find Diagonal check the complete row C8:P8 and highlight all diagonal) as found one more in the row 8 in cells M8, N10 & in O10 and continue same method to find all possible diagonal

Example data with numbers...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5		ROW N	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14
6		6					5		5	5
7		7					5	5				5	5
8		8	5	5	5	5			5			5	5		5
9		9		5	5	5			5				5	5	5	5
10		10			5			5		5	5	5	5		5
11		11	5				5			5	5
12		12	5	5		5	5				5
13		13	5		5	5				5	5	5	5	5
14		14			5	5					5		5
15		15		5		5	5		5			5		5	5	5
16		16	5	5		5	5				5	5		5	5
17		17		5			5			5					5
18		18		5	5	5					5					5
19		19	5		5		5		5	5	5	5		5		5
20		20			5			5		5		5			5	5
21		21	5	5	5				5	5	5	5		5	5
22		22	5	5		5		5		5		5			5
23		23	5	5	5				5		5				5	5
24		24		5	5	5	5	5	5	5	5	5		5		5
25		25		5	5				5	5		5
26		26		5				5				5	5	5		5
27		27	5		5	5		5							5
28		28	5	5	5			5	5		5	5		5		5
29		29		5				5	5	5	5	5	5		5
30		30				5	5		5	5			5
31		31	5	5	5			5			5		5			5
32		32			5			5	5	5
33		33				5	5				5	5	5
34		34	5				5			5	5		5	5	5
35		35	5		5			5	5			5		5	5	5
36		36	5		5		5	5		5		5		5	5
37		37				5	5	5	5	5	5	5	5	5	5
38		38	5	5		5			5	5			5	5	5
39		39	5	5	5	5				5						5
40		40		5			5	5		5						5
41		41	5	5	5	5				5		5		5		5
42		42	5						5		5		5	5		5
43		43		5		5	5	5	5		5	5		5
44		44	5	5			5	5	5	5	5		5	5		5
45		45		5					5		5					5
46		46	5	5	5			5			5	5		5
47		47	5		5		5	5	5		5	5	5	5		5
48		48	5		5		5	5			5			5
49		49		5	5	5		5					5
50		50			5	5			5		5	5
51		51		5	5		5	5	5	5		5		5	5	5
52		52					5		5				5			5
53		53	5	5		5		5	5		5	5	5			5
54		54		5			5	5				5		5	5	5
55		55					5	5					5	5	5
56		56	5	5						5	5	5	5	5		5
57		57		5	5			5	5			5		5	5
58		58		5	5			5					5	5		5
59		59	5			5				5		5
60		60				5		5		5	5	5	5	5
61		61	5					5		5		5
62		62	5	5		5	5		5	5	5	5	5	5		5
63		63		5	5					5			5	5	5
64		64		5	5	5		5			5		5
65		65	5		5					5	5	5	5	5	5
66		66						5	5	5	5		5	5	5
67		67		5	5		5		5	5			5		5
68		68		5								5			5
69		69		5		5	5				5				5
70		70	5			5	5	5				5				5
71		71		5				5		5	5	5
72		72				5	5	5		5	5	5
73		73	5	5	5					5	5					5
74		74	5		5	5		5		5				5
75		75	5	5		5	5		5				5	5	5
76		76			5		5			5		5		5		5
77		77						5				5
78		78		5						5			5
79		79
80		80
Sheet1

Example data with alphabets...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5		ROW N	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10	C11	C12	C13	C14
6		6					A		A	A
7		7					A	A				A	A
8		8	A	A	A	A			A			A	A		A
9		9		A	A	A			A				A	A	A	A
10		10			A			A		A	A	A	A		A
11		11	A				A			A	A
12		12	A	A		A	A				A
13		13	A		A	A				A	A	A	A	A
14		14			A	A					A		A
15		15		A		A	A		A			A		A	A	A
16		16	A	A		A	A				A	A		A	A
17		17		A			A			A					A
18		18		A	A	A					A					A
19		19	A		A		A		A	A	A	A		A		A
20		20			A			A		A		A			A	A
21		21	A	A	A				A	A	A	A		A	A
22		22	A	A		A		A		A		A			A
23		23	A	A	A				A		A				A	A
24		24		A	A	A	A	A	A	A	A	A		A		A
25		25		A	A				A	A		A
26		26		A				A				A	A	A		A
27		27	A		A	A		A							A
28		28	A	A	A			A	A		A	A		A		A
29		29		A				A	A	A	A	A	A		A
30		30				A	A		A	A			A
31		31	A	A	A			A			A		A			A
32		32			A			A	A	A
33		33				A	A				A	A	A
34		34	A				A			A	A		A	A	A
35		35	A		A			A	A			A		A	A	A
36		36	A		A		A	A		A		A		A	A
37		37				A	A	A	A	A	A	A	A	A	A
38		38	A	A		A			A	A			A	A	A
39		39	A	A	A	A				A						A
40		40		A			A	A		A						A
41		41	A	A	A	A				A		A		A		A
42		42	A						A		A		A	A		A
43		43		A		A	A	A	A		A	A		A
44		44	A	A			A	A	A	A	A		A	A		A
45		45		A					A		A					A
46		46	A	A	A			A			A	A		A
47		47	A		A		A	A	A		A	A	A	A		A
48		48	A		A		A	A			A			A
49		49		A	A	A		A					A
50		50			A	A			A		A	A
51		51		A	A		A	A	A	A		A		A	A	A
52		52					A		A				A			A
53		53	A	A		A		A	A		A	A	A			A
54		54		A			A	A				A		A	A	A
55		55					A	A					A	A	A
56		56	A	A						A	A	A	A	A		A
57		57		A	A			A	A			A		A	A
58		58		A	A			A					A	A		A
59		59	A			A				A		A
60		60				A		A		A	A	A	A	A
61		61	A					A		A		A
62		62	A	A		A	A		A	A	A	A	A	A		A
63		63		A	A					A			A	A	A
64		64		A	A	A		A			A		A
65		65	A		A					A	A	A	A	A	A
66		66						A	A	A	A		A	A	A
67		67		A	A		A		A	A			A		A
68		68		A								A			A
69		69		A		A	A				A				A
70		70	A			A	A	A				A				A
71		71		A				A		A	A	A
72		72				A	A	A		A	A	A
73		73	A	A	A					A	A					A
74		74	A		A	A		A		A				A
75		75	A	A		A	A		A				A	A	A
76		76			A		A			A		A		A		A
77		77						A				A
78		78		A						A			A
79		79
80		80
Sheet2

Thank you in advance

Kishan

 

[Kishan]

Hi Mick, the code is colouring alternate column in 2 different colours yellow & lime but do not highlighting the diagonals.

Please could you check?

Thank you

Kishan

Sorry Mick, may be for some reason your code does not work properly might cell were containing formula or they were not empty total or containing conditional formatting formula. Trying it again on fresh page it works like a charm

Thank you so much for you help and my apologize not checking it properly

Good Luck

Regards
Kishan

 

[Rick Rothstein]

I need to highlights 3 diagonals weather are they numbers or alphabets

Is the coloring you show in the number table example correct for the cells range H21:L24 (do you really want that lone yellow cell in cell I21)?

 

[Rick Rothstein]

Is the coloring you show in the number table example correct for the cells range H21:L24 (do you really want that lone yellow cell in cell I21)?

Just to follow up, the reason I asked the above question is that you did not follow the same rule in the range C21:F25. So in order to give you a proper solution, we need to know which color set is the correct one. Note: I did not look at the other solutions, but I find it hard to believe they reproduced the same colors as you show for the two ranges I have identified above.

 

[pgc01]

Hi Rick

If I understand correctly the cell I21 is not a lone cell, it's part of the diagonal I21,J22,K23

It just happens that the next row also generate the diagonal J22,K23,L24 and so the cells J22 and K23 belong to the 2 diagonals.

You'd have to choose which would be the colours of J22 and K23, since they are part of 2 diagonals. In the case the OP chose the colour of the diagonal in the lower row, blue.

 

[Rick Rothstein]

Hi Rick

If I understand correctly the cell I21 is not a lone cell, it's part of the diagonal I21,J22,K23

It just happens that the next row also generate the diagonal J22,K23,L24 and so the cells J22 and K23 belong to the 2 diagonals.

You'd have to choose which would be the colours of J22 and K23, since they are part of 2 diagonals. In the case the OP chose the colour of the diagonal in the lower row, blue.

I understand that, but did you see what I wrote in my follow up in Message #13 (the OP does not follow the same "rule" for coloring those cells)? I'm just asking him to either explain why they should be different or tell us which the wrong coloring set (or, conversely, which is the correct coloring set).

 

[MickG]

You're welcome
Glad it worked in the end.!!

 

[Rick Rothstein]

You're welcome
Glad it worked in the end.!!

I know the OP said your code worked for him, but it doesn't seem to work correctly for my. I do not get alternating colors (most of them are green with a small smattering of yellow ones mixed in). Also, in the two ranges I identified in Messages #12 and #13 (for the numbers table), your code colors them differently than the OP showed on my worksheet.

 

[MickG]

I know the colouring is not quite correct, but as duel colours where not explicitly requested and as I failed to get a quick correct result, I rather lost interest. !!! As you do !! and thought the code As Is, could be acceptable.
The resulting multicolouring is really set of 3 overlapping.

 

[MickG]

This is better !!!

[COLOR="Navy"]Sub[/COLOR] MG09Sep51
[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] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Tdn [COLOR="Navy"]As[/COLOR] Range, nDn [COLOR="Navy"]As[/COLOR] Range, fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C6"), Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] Ac = 0 To 11
        [COLOR="Navy"]Set[/COLOR] nDn = Union(Dn.Offset(, Ac), Dn.Offset(1, Ac + 1), Dn.Offset(2, Ac + 2))
        [COLOR="Navy"]Set[/COLOR] Tdn = Union(Dn, Dn.Offset(1, 1), Dn.Offset(2, 2))
        [COLOR="Navy"]If[/COLOR] Application.CountA(Tdn) = 3 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Dn.Offset(, Ac).Column = 3 [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                col = IIf(c Mod 2 = 0, vbGreen, vbYellow)
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Application.CountA(nDn) = 3 [COLOR="Navy"]Then[/COLOR]
                nDn.Interior.Color = col
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[pgc01]

I see your point now. Maybe there are inconsistencies in the colours, or else the OP will explain the logic.