Macro VBA/Conditional Formatting - click a cell and autofill with color in other cells from range ?

[realest25]

Hello dear members. Sorry, maybe it's a bit confusing to understand from the title what I'm asking you.

I want to create a small statistics tool.

If I click or double click on any cell with number, in this example the number 21, after I click on the number 21, to be automatically filled with green color the number 21 and also 9 neighbors to its left and 9 neighbors to his right

I want this to be possible for all numbers from 1 to 36.

After clicking on any number, to be changed the color of the clicked cell and also in 9 neighbors to its left and 9 neighbors to its right.

All cells that are empty (with no number) are of no importance, only cells with numbers are important.

Any number that is clicked to be able to highlight itself also 9 neighbors to its left and 9 neighbors to its right.

The needed color is green color from this excel.

Is it possible ?

Test Tool.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1
2			1	19	2	20	3	21	4	22	5	23					1	19	2	20	3	21	4	22	5	23
3
4		36											6			36											6
5
6		18											24			18											24
7
8		35											7			35											7
9
10		17											25			17											25
11
12		34											8			34											8
13
14		16											26			16											26
15
16		33											9			33											9
17
18		15											27			15											27
19
20			32	14	31	13	30	12	29	11	28	10					32	14	31	13	30	12	29	11	28	10
21
22
Sheet1

 

[Micron]

Possible by using SelectionChange event. The test would be to determine if the clicked cell contains a number and if so, colour the interior of the range that is 9 to the left and 9 to the right. If your picture is supposed to show the desired result, the description and the picture don't seem to agree.
Also, this sort of thing is more complicated than how I described it because if you click on 3 then 21, what should happen to what was 9 to the left and went green but is now 10 to the left?

 

[realest25]

Possible by using SelectionChange event. The test would be to determine if the clicked cell contains a number and if so, colour the interior of the range that is 9 to the left and 9 to the right. If your picture is supposed to show the desired result, the description and the picture don't seem to agree.
Also, this sort of thing is more complicated than how I described it because if you click on 3 then 21, what should happen to what was 9 to the left and went green but is now 10 to the left?

Thank you for your reply. SelectionChange event. I understand. I am just a rookie. I don't know how to create it.
I hope, if possible, if I click 3 then 21, to not be 10 to left and 10 to left but to be 9 lef and 9 right.
Maybe somehow, every time a new cell is clicked, the previous highlight to be cancelled and to be created a new highlight selection of 9 to left and 9 to right of the clicked cell ?

 

[Micron]

I would undo the whole row then apply the colour based on the target address. That is a value that the selectionChange event returns. Might have time to play, but will have to consider how to deal with the possibility that a cell with a number is clicked on and there are not 9 cells to the left or right of it. If that cannot be the case, it would be simpler to code.

 

[Micron]

how to deal with the possibility that a cell with a number is clicked on and there are not 9 cells to the left or right of it. If that cannot be the case, it would be simpler to code.

Waiting for a comment on that situation.

 

[realest25]

Waiting for a comment on that situation.

All I can say is that not all numbers that will be clicked, not all of them have 9 cells to the left and 9 to the right.
Maybe, if I can receive some kind of basic code, I will try to modify it afterwards.
All I need is the basic. I don't know how to create the basic vba/macro/SelectionChange.
Maybe it will be a very big help for me if I will receive an example of a code for just a number or two, with 3 cells to the left and 3 to the right.
After that I will try and adapt it according to my preferences.

 

[Micron]

OK; this will raise an error if the range cannot be set to 9 left and 9 right in the row of the clicked cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

If Application.WorksheetFunction.IsNumber(Target) Then
   Range(Cells(Target.Row, 1), Cells(Target.Row, 50)).Interior.ColorIndex = -4142
   Set rng = Range(Cells(Target.Row, Target.Column).Offset(0, -9), Cells(Target.Row, Target.Column).Offset(0, 9))
   rng.Interior.ColorIndex = 4
End If

End Sub

You could trap for this error, but seeing as how the number would be 1004 (I think) and there are umpteen messages for that number, I don't know if I would. I'd first try to determine if there are 9 cells to the right and 9 to the left of the selected cell. That test would probably be ok if 1004 error was raised since it would be specific to trying to determine that.

The rng object as I've utilized it may not be required for so simple an operation, but I coded for it because I thought there might be more that had to be done with it in terms of testing for 9 left and 9 right. Note: I used 50 as a test. That number is the number of columns in the row that I thought might come into play. You could adjust that number, or test for the last column with data in the clicked row instead.

 

[realest25]

OK; this will raise an error if the range cannot be set to 9 left and 9 right in the row of the clicked cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range

If Application.WorksheetFunction.IsNumber(Target) Then
   Range(Cells(Target.Row, 1), Cells(Target.Row, 50)).Interior.ColorIndex = -4142
   Set rng = Range(Cells(Target.Row, Target.Column).Offset(0, -9), Cells(Target.Row, Target.Column).Offset(0, 9))
   rng.Interior.ColorIndex = 4
End If

End Sub

You could trap for this error, but seeing as how the number would be 1004 (I think) and there are umpteen messages for that number, I don't know if I would. I'd first try to determine if there are 9 cells to the right and 9 to the left of the selected cell. That test would probably be ok if 1004 error was raised since it would be specific to trying to determine that.

The rng object as I've utilized it may not be required for so simple an operation, but I coded for it because I thought there might be more that had to be done with it in terms of testing for 9 left and 9 right. Note: I used 50 as a test. That number is the number of columns in the row that I thought might come into play. You could adjust that number, or test for the last column with data in the clicked row instead.

Thank you sir very much for your time, help and information ! I will use your code and adapt it wisely. Thank you again !

 

[Micron]

You're welcome and thanks for the acknowledgement.
I just realized that based on the number of cells left and right that need to be considered (9), it is as easy as making sure the target column is at least 10. So this will not error, and if there are not enough cells the row colour is removed. If you don't want the colour to be removed, move the line with -4142 inside the inner IF.

If Application.WorksheetFunction.IsNumber(Target) Then
   Range(Cells(Target.Row, 1), Cells(Target.Row, 50)).Interior.ColorIndex = -4142
   If Target.Column > 9 Then
      Set rng = Range(Cells(Target.Row, Target.Column).Offset(0, -9), Cells(Target.Row, Target.Column).Offset(0, 9))
      rng.Interior.ColorIndex = 4
   End If
End If

 

[realest25]

You're welcome and thanks for the acknowledgement.
I just realized that based on the number of cells left and right that need to be considered (9), it is as easy as making sure the target column is at least 10. So this will not error, and if there are not enough cells the row colour is removed. If you don't want the colour to be removed, move the line with -4142 inside the inner IF.

If Application.WorksheetFunction.IsNumber(Target) Then
   Range(Cells(Target.Row, 1), Cells(Target.Row, 50)).Interior.ColorIndex = -4142
   If Target.Column > 9 Then
      Set rng = Range(Cells(Target.Row, Target.Column).Offset(0, -9), Cells(Target.Row, Target.Column).Offset(0, 9))
      rng.Interior.ColorIndex = 4
   End If
End If

Thank you very much sir ! God Bless You !