Excel Formula to keep records of drawn cards

[Cquake]

In my excel sheet "home" cell A21 I have a card value number that comes up when activated by a macro that generates a new number that I would like to have those recorded in the order in which they are drawn in the same sheet "home" but from cells K1:Z40. Columns K,M,O,Q,S,U,W,Y are all pre-numbered to represent the draw number. IE: Colum K1:K40 would be listed as 1 to 30, Column M1 to M30 would be draw numbers 31 - 60, etc etc. Columns L,N,P,R,T,V,X,Z would be the the value of that draw. Any duplicate numbers drawn would show in bold red so I can spot how many times a number may be re-generated. Once all 240 cells have been filled a macro would be used to clear the current data and begin again. My question is what formula would be needed to accomplish this or is there a VBA code that would automatically accomplish this without the use of a macro activating it? As stated earlier the card value originates from A21. Any help would be appreciated.

K...........................L.............M.............N............O................P..............Q........................R.............S........................T............U.........................V..........W...........................X............Y..........................Z

1		31		61		91		121		151		181		211
2		32		62		92		122		152		182		212
3		33		63		93		123		153		183		213
4		34		64		94		124		154		184		214
5		35		65		95		125		155		185		215
6		36		66		96		126		156		186		216
7		37		67		97		127		157		187		217
8		38		68		98		128		158		188		218
9		39		69		99		129		159		189		219
10		40		70		100		130		160		190		220
11		41		71		101		131		161		191		221
12		42		72		102		132		162		192		222
13		43		73		103		133		163		193		223
14		44		74		104		134		164		194		224
15		45		75		105		135		165		195		225
16		46		76		106		136		166		196		226
17		47		77		107		137		167		197		227
18		48		78		108		138		168		198		228
19		49		79		109		139		169		199		229
20		50		80		110		140		170		200		230
21		51		81		111		141		171		201		231
22		52		82		112		142		172		202		232
23		53		83		113		143		173		203		233
24		54		84		114		144		174		204		234
25		55		85		115		145		175		205		235
26		56		86		116		146		176		206		236
27		57		87		117		147		177		207		237
28		58		88		118		148		178		208		238
29		59		89		119		149		179		209		239
30		60		90		120		150		180		210		240

 

[Special-K99]

There's probably a better way to write this (I'm not a VBA expert).
Ideally the quickest way to do this is to populate 2 cells with the last cell row and number (ie keep track of the blank cell that was last filled) and increment the row/column.

This code should be placed within your existing macro AFTER A21 is populated with a new card.

' Clear columns if fully populated
If Worksheets("Home").Range("Z40") <> "" Then
Range("L1:L40").Clear
Range("N1:N40").Clear
Range("P1:P40").Clear
Range("R1:R40").Clear
Range("T1:T40").Clear
Range("V1:V40").Clear
Range("X1:X40").Clear
Range("Z1:Z40").Clear
End If
' Find next available blank cell, exit from loop when found
For i = 12 To 26 Step 2
For j = 1 To 40
If Worksheets("Home").Cells(j, i) = "" Then
i1 = i: j1 = j: i = 27: j = 41
End If
Next j
Next i
' Populate the blank cell with the contents of A21
Worksheets("Home").Cells(j1, i1) = Worksheets("Home").Cells(21, 1)

 

[kvsrinivasamurthy]

For Conditional formatting. Select L1.
Formula

=SUMPRODUCT(1*(($L$1:$Z$30<>"")*($L$1:$Z$30=L1)*ISODD(COLUMN($L$1:$Z$1)-COLUMN($K$1))))>1

Select Font and Red Color.
Copy the format of Li to the full required range.
For Erasing Data after complete fill up use the worksheet event below.
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("L1:Z30")) Is Nothing Then
Dim T&
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Application.CountBlank(Range("L1:Z30")) = 0 Then
For T = 0 To 7
Range("L1:L30").Offset(0, T * 2).ClearContents
Next T
Application.EnableEvents = True
End If
End If
End Sub

How to use worksheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[Cquake]

Thank you both for your help.