Highlight offset 8th row 3 values, from the selected cell

[motilulla]

Hello,

I requiresome solution to be highlighted offset 8th row 3 values, from the selected cell
Range to beselected in columns K:P, and highlighted range to be selected in columns C:H

For example...
If I selectK16, highlight the C14:C16
If I selectM31, "clear the highlighted range C14:C16" and highlight the E29:E31
If I selectP47, "clear the highlighted range E29:E31" and highlight the H45:H47

Note: ifselection is out of columns K:P, clear all highlights from columns C:G

Exampledata....

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5			n1	n2	n3	n4	n5	n6			n1	n2	n3	n4	n5	n6
6			30	11	13	42	18	4			3	0	4	5	4	2
7			29	27	45	33	19	38			2	1	2	4	2	2
8			23	4	6	5	22	33			1	1	1	4	3	3
9			2	12	45	12	47	27			2	1	1	3	2	4
10			46	35	18	8	21	12			2	1	1	2	3	4
11			35	21	37	45	1	47			3	1	1	2	4	4
12			18	15	32	9	2	27			1	3	0	2	4	2
13			38	11	46	5	13	9			2	3	1	3	4	2
14			45	4	2	30	9	38			2	5	2	4	2	2
15			45	10	18	6	3	10			3	3	2	4	2	4
16			3	16	22	21	1	46			3	2	1	4	2	5
17
18
19
20			n1	n2	n3	n4	n5	n6			n1	n2	n3	n4	n5	n6
21			30	11	13	42	18	4			3	0	4	5	4	2
22			29	27	45	33	19	38			2	1	2	4	2	2
23			23	4	6	5	22	33			1	1	1	4	3	3
24			2	12	45	12	47	27			2	1	1	3	2	4
25			46	35	18	8	21	12			2	1	1	2	3	4
26			35	21	37	45	1	47			3	1	1	2	4	4
27			18	15	32	9	2	27			1	3	0	2	4	2
28			38	11	46	5	13	9			2	3	1	3	4	2
29			45	4	2	30	9	38			2	5	2	4	2	2
30			45	10	18	6	3	10			3	3	2	4	2	4
31			3	16	22	21	1	46			3	2	1	4	2	5
32
33
34
35
36			n1	n2	n3	n4	n5	n6			n1	n2	n3	n4	n5	n6
37			30	11	13	42	18	4			3	0	4	5	4	2
38			29	27	45	33	19	38			2	1	2	4	2	2
39			23	4	6	5	22	33			1	1	1	4	3	3
40			2	12	45	12	47	27			2	1	1	3	2	4
41			46	35	18	8	21	12			2	1	1	2	3	4
42			35	21	37	45	1	47			3	1	1	2	4	4
43			18	15	32	9	2	27			1	3	0	2	4	2
44			38	11	46	5	13	9			2	3	1	3	4	2
45			45	4	2	30	9	38			2	5	2	4	2	2
46			45	10	18	6	3	10			3	3	2	4	2	4
47			3	16	22	21	1	46			3	2	1	4	2	5
48
49
Sheet1

Thank youall
Excel 2000
Regards,
Moti

 

[motilulla]

Is it? I presumed if you selected a cell near the top of the right-hand filled range, or if you selected a cell in the title row itself, that you would not want the selection in the other filled range to color the title row or blank cells above it. Mick's code colors those cells for such a selection... the following SelectChange event accounts doesn't.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim TopRow As Long, Cell As Range, Rng As Range, IntersectRng As Range
  Range("C:H,K:P").Interior.ColorIndex = xlColorIndexNone
  Set Rng = Intersect(Target, Columns("K:P"))
  If Not Rng Is Nothing Then
    TopRow = Rng.CurrentRegion.Row
    For Each Cell In Rng
      If IsNumeric(Cell.Value) And Cell.Value <> "" Then
        Cell.Interior.Color = vbYellow
        Intersect(Cell.CurrentRegion.Offset(1, -8), Cell.Offset(-2, -8).Resize(3)).Interior.Color = vbYellow
      End If
    Next
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hello Rick Rothstein, after Appling your function when I select any cell in the column K:P, it start flickering, and to exit code I need to press escape key after this is what happen. I did select the cell M12

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3
4
5			n1	n2	n3	n4	n5	n6			n1	n2	n3	n4	n5	n6
6			30	11	13	42	18	4			3	0	4	5	4	2
7			29	27	45	33	19	38			2	1	2	4	2	2
8			23	4	6	5	22	33			1	1	1	4	3	3
9			2	12	45	12	47	27			2	1	1	3	2	4
10			46	35	18	8	21	12			2	1	1	2	3	4
11			35	21	37	45	1	47			3	1	1	2	4	4
12			18	15	32	9	2	27			1	3	0	2	4	2
13			38	11	46	5	13	9			2	3	1	3	4	2
14			45	4	2	30	9	38			2	5	2	4	2	2
15			45	10	18	6	3	10			3	3	2	4	2	4
16			3	16	22	21	1	46			3	2	1	4	2	5
17
18
19
Sheet1

Please could you check?

Thank you for your help

Kind Regards,
Moti

 

[Rick Rothstein]

Hello Rick Rothstein, after Appling your function when I select any cell in the column K:P, it start flickering, and to exit code I need to press escape key after this is what happen. I did select the cell M12

I am not sure what to tell you as my code does not do what you are describing for me... when I pick cell M12, cells E10:E12 highlight in yellow without any flickering (and the same for any other cell with a number in it in Columns K:P).

 

[motilulla]

I am not sure what to tell you as my code does not do what you are describing for me... when I pick cell M12, cells E10:E12 highlight in yellow without any flickering (and the same for any other cell with a number in it in Columns K:P).

Hello RickRothstein, I tried with a new sheet no luck, thank you for looking in to it. I adapted Mick code with my necessity and it is working ok. I have a next question please need your support how can I combine two " Private SubWorksheet_SelectionChange(ByVal Target As Range)" in to the one sheet

Code1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("CO:DB")) Is Nothing Then
    If Target <> "" Then
        Range("C6:P5006").Interior.ColorIndex = xlNone
        Target.Offset(-2, -90).Resize(3).Interior.Color = vbYellow
    End If
ElseIf Intersect(Target, Columns("CO:DB")) Is Nothing Then
     Range("C6:P5006").Interior.ColorIndex = xlNone
End If
End If
End Sub

Code2

Private Sub Worksheet_SelectionChange_1(ByVal Target As Range)
If Target.Count = 1 Then
If Not Intersect(Target, Range("CF:CF")) Is Nothing Then
    If Target <> "" Then
        Range("C6:P5006").Interior.ColorIndex = xlNone
        Target.Offset(0, -81).Resize(1, 14).Interior.Color = vbYellow
        Target.Offset(-2, -81).Resize(1, 14).Interior.Color = vbYellow
    End If
ElseIf Intersect(Target, Columns("CF:CF")) Is Nothing Then
     Range("C6:P5006").Interior.ColorIndex = xlNone
End If
End If
End Sub

Thank you for your help

Kind Regards,
Moti

 

[Rick Rothstein]

Hello Rick Rothstein, after Appling your function when I select any cell in the column K:P, it start flickering, and to exit code I need to press escape key after this is what happen. I did select the cell M12

Hello RickRothstein, I tried with a new sheet no luck,

I cannot think of any reason why my code won't work for you. Can you do me a favor and send me a copy of your workbook so that I can watch my code work against your actual data? My email address is...

rick DOT news AT verizon DOT net

 

[motilulla]

I cannot think of any reason why my code won't work for you. Can you do me a favor and send me a copy of your workbook so that I can watch my code work against your actual data? My email address is...

rick DOT news AT verizon DOT net

Hello Rick, please check workbook sent

Kind Regars,
Moti

 

[Rick Rothstein]

Hello Rick, please check workbook sent

I got the file, opened it, enabled macros and... the code worked perfectly, it highlighted the correct cells exactly as it was supposed to. I opened originally in XL2010 (the latest version I currently have installed) so, thinking it may be a version issue, I opened it in XL2003 (the earliest version I own) and, again, the code worked perfectly for me. I cannot duplicate the problem you say you are having even when using your original file. Just out of curiosity, what version of Excel are you using?

 

[motilulla]

I got the file, opened it, enabled macros and... the code worked perfectly, it highlighted the correct cells exactly as it was supposed to. I opened originally in XL2010 (the latest version I currently have installed) so, thinking it may be a version issue, I opened it in XL2003 (the earliest version I own) and, again, the code worked perfectly for me. I cannot duplicate the problem you say you are having even when using your original file. Just out of curiosity, what version of Excel are you using?

Hello Rick Rothstein, it is mention in post#1 in the left of bottom "Excel 2000". I believe that code has no issue; it could be the version problem.

Could you please check post#13, is it possible to use 2 function in the one worksheet

Thank you

Kind Regards,
Moti