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]

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)?

Hi Rick Rothstein, Thank you for asking a question here is my reply what happens and how I filled the colours when I start finding 3 diagonal manually to place my query example sheet on the "MrExcel Message Board"

How I filled the colours to highlights the diagonals I try to explain continuation...

1-I find the first set of diagonal in cells C8, D9, and E10 filled these cells with yellow colour as in row8 I find another diagonal in cells M8, N9, and O10 also filled these cells with yellow colour.

2-In the row 10, 11 and 12 find 2 diagonals filled them with another colour Cyan just to show the difference from one to another.

3-In the row 16, 17 and 18 find 2 diagonals filled them with first colour yellow.

4-here is the answer of your question finds 3 diagonal in the row 21....
1st-C21, D22, & E23, 2nd-I21, J22, & K23, 3rd-M21, N22, & O23 I did fill these cells with colour yellow.
After that I went to row 22 find 1st-C22, D23 & E24 filled with colour Cyan, find 2nd- H22, I23 & J24 filled with colour Cyan, 3rd- J22, K23 & L24 also fill with colour Cyan overwriting colour cyan yellow despaired. Now I see there is 4th-diagonal which I missed D22, E23 & F24 I would have fill Cyan too in it.

Please see the below if I would have notice of 4th diagonal how it would have looked
Error correct in the row finds 4 not 3 and colour in cells D22, E23 & F24 Cyan

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
Sheet1

It all has happened just overwriting them without following any rule while colouring jut it was to identify the diagonals

I hope I have explained the method how I followed colouring the diagonals

Have a nice day

Good Luck

Regards,
Kishan

 

[Kishan]

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

Thank you Mick, for looking in to it again and making it as per request.

It is fine I appreciate your help

Good Luck

Regards
Kishan

 

[Rick Rothstein]

4-here is the answer of your question finds 3 diagonal in the row 21....
1st-C21, D22, & E23, 2nd-I21, J22, & K23, 3rd-M21, N22, & O23 I did fill these cells with colour yellow.
After that I went to row 22 find 1st-C22, D23 & E24 filled with colour Cyan, find 2nd- H22, I23 & J24 filled with colour Cyan, 3rd- J22, K23 & L24 also fill with colour Cyan overwriting colour cyan yellow despaired. Now I see there is 4th-diagonal which I missed D22, E23 & F24 I would have fill Cyan too in it.

That is what we needed know. Now, I don't know if this will work in your version of Excel (XL2000) as i cannot find information on what version the SpecialCells function was introduced. But if your version does support it, then this macro should do what you want...

[table="width: 500"]
[tr]
	[td]Sub HighlightDiagonalTriples()
  Dim ColCell As Range, RowCell As Range, TripletColor As Long
  TripletColor = rgbYellow
  For Each ColCell In Range("C6", Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlConstants)
    If ColCell.Value = ColCell.Offset(1, 1).Value And ColCell.Value = ColCell.Offset(2, 2).Value Then
      For Each RowCell In Range(Cells(ColCell.Row, "C"), Cells(ColCell.Row, "P")).SpecialCells(xlConstants)
        If RowCell.Value = RowCell.Offset(1, 1).Value And RowCell.Value = RowCell.Offset(2, 2).Value Then Union(RowCell, RowCell.Offset(1, 1), RowCell.Offset(2, 2)).Interior.Color = TripletColor
      Next
      TripletColor = rgbYellow + rgbLime - TripletColor
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[Kishan]

That is what we needed know. Now, I don't know if this will work in your version of Excel (XL2000) as i cannot find information on what version the SpecialCells function was introduced. But if your version does support it, then this macro should do what you want...

Hi Rick Rothstein, Thank you for the macro, you already had uncertainty about the version will it work or not, it is resulting as shown

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
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
Sheet3

Please could you check?

Regards,
Kishan

 

[Rick Rothstein]

Hi Rick Rothstein, Thank you for the macro, you already had uncertainty about the version will it work or not, it is resulting as shown

Please could you check?

The fact that it is "coloring" something means the SpecialCells function might be working in your version of Excel (I cannot be sure because the pattern looks a little different than I would be expecting). The reason you are getting all black cells is probably because your version does not have the rgbXXXX color constants defined in it (that must have come in a later version). Here is my code using normal VB color constants instead, see if that works for you...

[table="width: 500"]
[tr]
	[td]Sub HighlightDiagonalTriples()
  Dim ColCell As Range, RowCell As Range, TripletColor As Long
  TripletColor = vbYellow
  For Each ColCell In Range("C6", Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlConstants)
    If ColCell.Value = ColCell.Offset(1, 1).Value And ColCell.Value = ColCell.Offset(2, 2).Value Then
      For Each RowCell In Range(Cells(ColCell.Row, "C"), Cells(ColCell.Row, "P")).SpecialCells(xlConstants)
        If RowCell.Value = RowCell.Offset(1, 1).Value And RowCell.Value = RowCell.Offset(2, 2).Value Then Union(RowCell, RowCell.Offset(1, 1), RowCell.Offset(2, 2)).Interior.Color = TripletColor
      Next
      TripletColor = vbYellow + vbGreen - TripletColor
    End If
  Next
End Sub[/td]
[/tr]
[/table]

NOTE: My code is expecting your cells to either be blank or filled with constants (that is, there should be no formulas in any of the cells).

 

[Kishan]

The fact that it is "coloring" something means the SpecialCells function might be working in your version of Excel (I cannot be sure because the pattern looks a little different than I would be expecting). The reason you are getting all black cells is probably because your version does not have the rgbXXXX color constants defined in it (that must have come in a later version). Here is my code using normal VB color constants instead, see if that works for you...

Hi Rick Rothstein, it is better but now but not exactly, as it should be.
Please check the results

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
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
81
82
83
84
Sheet3

Thank you

Regards,
Kishan

 

[Rick Rothstein]

Hi Rick Rothstein, it is better but now but not exactly, as it should be.

Are your blank cells actually blank or could they have maybe space characters in them? You can test this by executing the following line of code in the VB editor's Immediate Window. Using cell G8 because it is colored but has no text in it, execute this line of code there and tell me the result...

? Len(Range("G8").Value)

Also, just verifying... you do not have any formulas in Columns C:P, correct?

 

[Kishan]

Are your blank cells actually blank or could they have maybe space characters in them? You can test this by executing the following line of code in the VB editor's Immediate Window. Using cell G8 because it is colored but has no text in it, execute this line of code there and tell me the result...

? Len(Range("G8").Value)

Also, just verifying... you do not have any formulas in Columns C:P, correct?

Hi Rick Rothstein, I was struggling who to use your formula "Len(Range("G8").Value)" and where to place it and how to get value for the cell G8 this was new for me finally I get it done and find G8 were showing Range ("G8").Value = "" so I done some test and select the range A1:S84 replaced empty cell with 0 and then replaced 0 with nothing and run the code it give the desire results.

Here are the results I think now all is working OK!!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
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
81
82
83
84
Sheet3

Thank you so much for your help

Have a nice day

Good Luck

Regards,
Kishan

 

[Rick Rothstein]

Hi Rick Rothstein, I was struggling who to use your formula "Len(Range("G8").Value)" and where to place it and how to get value for the cell G8 this was new for me finally I get it done and find G8 were showing Range ("G8").Value = "" so I done some test and select the range A1:S84 replaced empty cell with 0 and then replaced 0 with nothing and run the code it give the desire results.

You were supposed to execute the code line I posted in the VBA editor's Immediate Window, not on the worksheet. And the question mark is part of the code line (it is the shortcut for the Print keyword). No matter, I think you have told me enough to figure out what needs to be done to make my code work on your data. See if this revised code works on the data it previously failed on...

[table="width: 500"]
[tr]
	[td]Sub HighlightDiagonalTriples()
  Dim ColCell As Range, RowCell As Range, TripletColor As Long
  With Range("C6", Cells(Rows.Count, "C").End(xlUp))
    .Value = .Value
    TripletColor = vbYellow
    For Each ColCell In .SpecialCells(xlConstants)
      If ColCell.Value = ColCell.Offset(1, 1).Value And ColCell.Value = ColCell.Offset(2, 2).Value Then
        For Each RowCell In Range(Cells(ColCell.Row, "C"), Cells(ColCell.Row, "P")).SpecialCells(xlConstants)
          If RowCell.Value = RowCell.Offset(1, 1).Value And RowCell.Value = RowCell.Offset(2, 2).Value Then Union(RowCell, RowCell.Offset(1, 1), RowCell.Offset(2, 2)).Interior.Color = TripletColor
        Next
        TripletColor = vbYellow + vbGreen - TripletColor
      End If
    Next
  End With
End Sub[/td]
[/tr]
[/table]

 

[Kishan]

You were supposed to execute the code line I posted in the VBA editor's Immediate Window, not on the worksheet. And the question mark is part of the code line (it is the shortcut for the Print keyword). No matter, I think you have told me enough to figure out what needs to be done to make my code work on your data. See if this revised code works on the data it previously failed on...

Hi Rick Rothstein, Ok I got it how to execute the line code " Len(Range("G8").Value)" in the VBA editor's Immediate Window pointing mouse on the code line I can see this (Range ("G8").Value = "")
I run the new code from the post# 29 no luck it is resulting as shown in the post#26

Thank you

Regards,
Kishan