Need the Uncoloured Cells after double click to be listed in Sheet 2

[dorcar90]

After doing the following to make cells coloured after double clicking them:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Interior.ColorIndex = xlNone Then

' Then change the background color to red
Target.Interior.ColorIndex = 8

' Else if the cell background color is red
ElseIf Target.Interior.ColorIndex = 8 Then

' Then clear the background
Target.Interior.ColorIndex = xlNone

End If

' This is to prevent the cell from being edited when double-clicked
Cancel = True

End Sub

...now I've been stucked of how am I going to do those numbers who are still uncoloured (Range A3:P23) (not double clicked..meaning missing for me) to be added in a list in Sheet 2 column A3 down..

 

[GTO]

Hi,

FWIW, for the double-click, you might wish to limit its execution to the range of interest, something like:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> Target.Count = 1 And <SPAN style="color:#00007F">Not</SPAN> Application.Intersect(Me.Range("A1:P23"), Target) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Cancel = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Target.Interior.ColorIndex = xlNone <SPAN style="color:#00007F">Then</SPAN><br>            Target.Interior.ColorIndex = 8<br>        <SPAN style="color:#00007F">ElseIf</SPAN> Target.Interior.ColorIndex = 8 <SPAN style="color:#00007F">Then</SPAN><br>            Target.Interior.ColorIndex = xlNone<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

For plunking the uncolored cell vaalues in another sheet, maybe like:

In a Standard Module:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#007F00">'now I've been stucked of how am I going to do those numbers who are still uncoloured</SPAN><br><SPAN style="color:#007F00">'(Range A3:P23) (not double clicked..meaning missing for me) to be added in a list in</SPAN><br><SPAN style="color:#007F00">'Sheet 2 column A3 down..</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> example()<br><SPAN style="color:#00007F">Dim</SPAN> rngColored                  <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> Cell                        <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> aryColored()                <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> aryOutput(1 <SPAN style="color:#00007F">To</SPAN> 336, 1 <SPAN style="color:#00007F">To</SPAN> 1) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> x                           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> y                           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> lCounter                    <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngColored = Sheet1.Range("A3:P23")<br>    aryColored = rngColored.Value<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> rngColored.Cells<br>        <SPAN style="color:#00007F">If</SPAN> Cell.Interior.ColorIndex = 8 <SPAN style="color:#00007F">Then</SPAN><br>            aryColored(Cell.Row - 2, Cell.Column) = vbNullString<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    lCounter = 0<br>    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> 21<br>        <SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> 16<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> aryColored(x, y) = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>                lCounter = lCounter + 1<br>                aryOutput(lCounter, 1) = aryColored(x, y)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    Sheet2.Range("A3").Resize(336).Value = aryOutput<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Please note the 'Sheet1' and 'Sheet2' represent CodeNames of the sheets.

Hope that helps,

Mark

 

[dorcar90]

The first part of the colour worked..

but in Sheet 2 I am still not shown any numbers (just the value) that I have not double clicked to colour them..

..can I make a system where in sheet 2 I enter the same numbers in the same cells so that those that I colour will remove the number in Sheet 2..so that those that I do not colour will remain in Sheet 2..?

I am making this system to ease missing invoices...those that are not coloured means missing as I have not ticked them..

 

[GTO]

...but in Sheet 2 I am still not shown any numbers (just the value) that I have not double clicked to colour them...

I am afraid I do not understand what you mean by "numbers" vs. "value".

 

[dorcar90]

Numbers are just for my info (as every invoice has its own registered number) .. its not a number to calculate a sum..its just like text..

 

[GTO]

I am afraid I am still not grasping. You want the values (whether text or numerical) from the cells with no color - correct?

With this:

Excel 2000
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	81	36	111	37	TEXT	93	1	47	65	99	123	41	107	31	45	68
4	72	50	22	2	112	67	122	79	70	114	110	47	87	14	36	43
5	36	64	76	43	63	83	28	119	30	43	119	88	38	101	91	86
6	32	18	89	112	44	120	76	64	28	59	73	6	84	63	23	121
7	83	76	77	83	49	66	90	37	86	85	67	79	40	111	5	79
8	70	100	116	48	79	11	46	39	64	38	119	94	61	0	36	12
9	71	15	98	109	78	41	21	15	47	102	8	114	51	31	10	47
10	32	57	94	40	109	93	2	58	20	20	2	68	52	33	21	3
11	7	103	91	70	45	63	46	115	55	27	80	33	9	108	60	119
12	114	26	71	107	45	40	41	96	49	8	75	26	119	84	114	41
13	98	50	55	47	113	14	97	43	120	52	69	44	71	119	74	75
14	87	37	58	48	100	45	85	48	105	20	37	82	52	119	24	1
15	47	115	34	16	97	39	66	74	22	49	26	91	24	7	33	115
16	106	55	21	92	65	39	15	82	119	77	82	81	32	12	83	88
17	86	36	44	77	108	23	17	58	115	84	5	116	61	83	91	109
18	73	80	95	101	102	19	6	88	73	103	113	29	3	109	22	37
19	98	42	53	58	32	81	1	48	28	6	30	45	99	107	54	80
20	100	93	103	6	102	90	104	79	103	84	89	75	118	105	82	31
21	66	12	106	89	80	55	8	94	85	2	29	9	104	120	90	122
22	111	46	96	120	35	116	106	1	0	13	69	54	100	118	66	20
23	110	63	14	12	96	51	67	16	115	46	118	120	73	66	31	2
Sheet1

I get this (on Sheet2):

Excel 2000
	A
3	81
4	36
5	37
6	TEXT
7	47
8	99
9	123
10	41
11	107
12	110
13	63
14	16
15	115
16	46
17	118
18	120
19	73
20	66
21	31
22	2
Sheet2

Is that not what we are after?

Sorry I am not understanding the goal so far. Don't give up, I'm sure it (whatever "it" is at the moment) can be done

Mark

 

[dorcar90]

Exactly you understood now!!!

Yes like you did in the images...

 

[GTO]

Exactly you understood now!!!

Yes like you did in the images...

Okay, the code at post #2 does that. Specifically, the procedure 'example()'. What is it doing (or not doing) for you?