Automatically Center Across Selection and Fill Based on Number in another Cell

[RLBrown]

I'm working on an event calendar and need to have the event listed in the week it starts then fill color in cells for the number of weeks that the event will run.
If possible, centering the event across the selection horizontally would be ideal. The the weeks listed in the calendar will never change as there is no 'date'.
Worksheet showing where I am currently and my end goal is attached. --- Thank You To All.

 

[mumps]

You are very welcome.

 

[RLBrown]

You are very welcome.

(No good deed goes unpunished -- Oscar Wilde)

As stated above, your solution worked beautifully --- until I added a new promo (row). How or what do I need to do to enable (prompt?) the macro to apply to new rows?

 

[mumps]

If the new row is directly underneath, the macro should work as written. If it doesn’t work, please post an updated sheet.

 

[RLBrown]

Kroger Promo Master Jan2022 v2 practice.xlsm
	A		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	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1				Calendar 2022							P1 WK1				P2 WK1				P3				P4				P5				P6				P7				P8				P9				P10				P11				P12				P13
2	Pd'		Brand	Description	Promo Type	Promo Start	# of Weeks	Contract Status	Contract Due	Promo Price	P1 WK1	P1 WK2	P1 WK3	P1 WK4	P2 WK1	P2 WK2	P2 WK3	P2 WK4	P3 WK1	P3 WK2	P3 WK3	P3 WK4	P4 WK1	P4 WK2	P4 WK3	P4 WK4	P5 WK1	P5 WK2	P5 WK3	P5 WK4	P6 WK1	P6 WK2	P6 WK3	P6 WK4	P7 WK1	P7 WK2	P7 WK3	P7 WK4	P8 WK1	P8 WK2	P8 WK3	P8 WK4	P9 WK1	P9 WK2	P9 WK3	P9 WK4	P10 WK1	P10 WK2	P10 WK3	P10 WK4	P11 WK1	P11 WK2	P11 WK3	P11 WK4	P12 WK1	P12 WK2	P12 WK3	P12 WK4	P13 WK1	P13 WK2	P13 WK3	P13 WK4
3	P2		BMBE	CL 5OZ	MEGA	P2 WK1	2		11/19/21	$0.69					CL 5OZ MEGA
4	P2		BMBE	CL 5OZ	KATS	P2 WK3	4		12/03/21	$0.79
5	P3		BMBE	CL 5OZ	EDLP	P3 WK3	12		12/31/21	$0.99
6	P5		BMBE	CL 5OZ	KATS	P5 WK1	6		02/11/22	$0.79
7	P2		BMBE	POUCH 2.5OZ	KATS	P2 WK1	4		11/19/21	10/$10					POUCH 2.5OZ KATS
8	P3		BMBE	POUCH 2.5OZ	EDLP	P3 WK1	13		12/17/21	4/$5
9	P4		BMBE	POUCH 2.5OZ	KATS	P4 WK3	8		01/28/22	10/$10
10	P1		BMBE	CL 12OZ	EDLP	P1 WK1	6		10/22/21	$2.79	CL 12OZ EDLP
11	P3		BMBE	CL 12OZ	EDLP	P3 WK2	13		12/24/21	$2.79
12	P3		BMBE	SW 4CT 5OZ	EDLP	P3 WK2	13		12/24/21	$9.79
13	P2		BMBE	CW 5OZ	EDLP	P2 WK2	9		11/26/21	$1.69
14	P2		BMBE	CW 5OZ	KATS	P2 WK2	4		11/26/21	3/$4
15	P4		BMBE	CW 5OZ	EDLP	P4 WK3	8		01/28/22	$1.79
16	P5		BMBE	CW 5OZ	KATS	P5 WK1	4		02/11/22	3/$4
17	P2		BMBE	CL 4CT 5OZ	SEAS LOW	P2 WK2	9		11/26/21	$6.99
18	P2		BMBE	PRIME 5OZ	KATS	P2 WK1	4		11/19/21	2/$5					PRIME 5OZ KATS
19	P2		BMBE	SW 12OZ	SEAS LOW	P2 WK2	9		11/26/21	$3.99
20	P3		BMBE	SW 3CT 3OZ	EDLP	P3 WK2	13		12/24/21	$3.49
21	P2		BMBE	SW 5OZ	SEAS LOW	P2 WK2	9		11/26/21	$1.99
22	P1		BMBE	CL 3CT 3OZ	EDLP	P1 WK1	5		10/22/21	$3.99	CL 3CT 3OZ EDLP
23	P1		BMBE	KITS SOTR	EDLP	P1 WK1	5		10/22/21	4/$6	KITS SOTR EDLP
24	P2		BMBE	KITS SOTR	SEAS LOW	P2 WK2	9		11/26/21	4/$6
25	P2		BMBE	TRAD. SARD.	SEAS LOW	P2 WK2	9		11/26/21	10/$10
26	P3		BMBE	SALMON - RED TALLS	KATS	P3 WK1	4		12/17/21	$3.99
27	P2		BMBE	SALMON - RED <14OZ	KATS	P2 WK1	4		11/19/21	$4.99					SALMON - RED <14OZ KATS
28	P2		BMBE	CLAMS	KATS	P2 WK1	8		11/19/21	3/$5					CLAMS KATS
29	P2		BMBE	CLAM JUICE	KATS	P2 WK1	8		11/19/21	2/$4					CLAM JUICE KATS
30	P2		BMBE	OYSTERS	KATS	P2 WK1	4		11/19/21	2/$4					OYSTERS KATS
31									#N/A
32									#N/A
005PromoMaster

Cell Formulas
Range	Formula
I3:I32	I3	=VLOOKUP($F3,Lookup!$R$2:$S$53,2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:I	Cell	contains an error	text	NO
H3:H50	Cell Value	contains "SUBMITTED"	text	NO
H3:H50	Cell Value	contains "REJECTED"	text	NO
H3:H50	Cell Value	contains "ACCEPTED"	text	NO
K3:BJ47	Cell	does not contain a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A3:A59	List	=Lookup!$M$1:$M$14
C3:C50	List	=Lookup!L$11:L$16
D3	List	=Lookup!$O$3:$O$35
E3:E102	List	=Lookup!$L$1:$L$9
F3:F97	List	=Lookup!R$1:R$53
D4:D50	List	=Lookup!$O$2:$O$35
H3:H32	List	=Lookup!X$1:X$4

 

[RLBrown]

Kroger Promo Master Jan2022 v2 practice.xlsm
	A		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	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1				Calendar 2022							P1 WK1				P2 WK1				P3				P4				P5				P6				P7				P8				P9				P10				P11				P12				P13
2	Pd'		Brand	Description	Promo Type	Promo Start	# of Weeks	Contract Status	Contract Due	Promo Price	P1 WK1	P1 WK2	P1 WK3	P1 WK4	P2 WK1	P2 WK2	P2 WK3	P2 WK4	P3 WK1	P3 WK2	P3 WK3	P3 WK4	P4 WK1	P4 WK2	P4 WK3	P4 WK4	P5 WK1	P5 WK2	P5 WK3	P5 WK4	P6 WK1	P6 WK2	P6 WK3	P6 WK4	P7 WK1	P7 WK2	P7 WK3	P7 WK4	P8 WK1	P8 WK2	P8 WK3	P8 WK4	P9 WK1	P9 WK2	P9 WK3	P9 WK4	P10 WK1	P10 WK2	P10 WK3	P10 WK4	P11 WK1	P11 WK2	P11 WK3	P11 WK4	P12 WK1	P12 WK2	P12 WK3	P12 WK4	P13 WK1	P13 WK2	P13 WK3	P13 WK4
3	P2		BMBE	CL 5OZ	MEGA	P2 WK1	2		11/19/21	$0.69					CL 5OZ MEGA
4	P2		BMBE	CL 5OZ	KATS	P2 WK3	4		12/03/21	$0.79
5	P3		BMBE	CL 5OZ	EDLP	P3 WK3	12		12/31/21	$0.99
6	P5		BMBE	CL 5OZ	KATS	P5 WK1	6		02/11/22	$0.79
7	P2		BMBE	POUCH 2.5OZ	KATS	P2 WK1	4		11/19/21	10/$10					POUCH 2.5OZ KATS
8	P3		BMBE	POUCH 2.5OZ	EDLP	P3 WK1	13		12/17/21	4/$5
9	P4		BMBE	POUCH 2.5OZ	KATS	P4 WK3	8		01/28/22	10/$10
10	P1		BMBE	CL 12OZ	EDLP	P1 WK1	6		10/22/21	$2.79	CL 12OZ EDLP
11	P3		BMBE	CL 12OZ	EDLP	P3 WK2	13		12/24/21	$2.79
12	P3		BMBE	SW 4CT 5OZ	EDLP	P3 WK2	13		12/24/21	$9.79
13	P2		BMBE	CW 5OZ	EDLP	P2 WK2	9		11/26/21	$1.69
14	P2		BMBE	CW 5OZ	KATS	P2 WK2	4		11/26/21	3/$4
15	P4		BMBE	CW 5OZ	EDLP	P4 WK3	8		01/28/22	$1.79
16	P5		BMBE	CW 5OZ	KATS	P5 WK1	4		02/11/22	3/$4
17	P2		BMBE	CL 4CT 5OZ	SEAS LOW	P2 WK2	9		11/26/21	$6.99
18	P2		BMBE	PRIME 5OZ	KATS	P2 WK1	4		11/19/21	2/$5					PRIME 5OZ KATS
19	P2		BMBE	SW 12OZ	SEAS LOW	P2 WK2	9		11/26/21	$3.99
20	P3		BMBE	SW 3CT 3OZ	EDLP	P3 WK2	13		12/24/21	$3.49
21	P2		BMBE	SW 5OZ	SEAS LOW	P2 WK2	9		11/26/21	$1.99
22	P1		BMBE	CL 3CT 3OZ	EDLP	P1 WK1	5		10/22/21	$3.99	CL 3CT 3OZ EDLP
23	P1		BMBE	KITS SOTR	EDLP	P1 WK1	5		10/22/21	4/$6	KITS SOTR EDLP
24	P2		BMBE	KITS SOTR	SEAS LOW	P2 WK2	9		11/26/21	4/$6
25	P2		BMBE	TRAD. SARD.	SEAS LOW	P2 WK2	9		11/26/21	10/$10
26	P3		BMBE	SALMON - RED TALLS	KATS	P3 WK1	4		12/17/21	$3.99
27	P2		BMBE	SALMON - RED <14OZ	KATS	P2 WK1	4		11/19/21	$4.99					SALMON - RED <14OZ KATS
28	P2		BMBE	CLAMS	KATS	P2 WK1	8		11/19/21	3/$5					CLAMS KATS
29	P2		BMBE	CLAM JUICE	KATS	P2 WK1	8		11/19/21	2/$4					CLAM JUICE KATS
30	P2		BMBE	OYSTERS	KATS	P2 WK1	4		11/19/21	2/$4					OYSTERS KATS
31									#N/A
32									#N/A
005PromoMaster

Cell Formulas
Range	Formula
I3:I32	I3	=VLOOKUP($F3,Lookup!$R$2:$S$53,2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:I	Cell	contains an error	text	NO
H3:H50	Cell Value	contains "SUBMITTED"	text	NO
H3:H50	Cell Value	contains "REJECTED"	text	NO
H3:H50	Cell Value	contains "ACCEPTED"	text	NO
K3:BJ47	Cell	does not contain a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A3:A59	List	=Lookup!$M$1:$M$14
C3:C50	List	=Lookup!L$11:L$16
D3	List	=Lookup!$O$3:$O$35
E3:E102	List	=Lookup!$L$1:$L$9
F3:F97	List	=Lookup!R$1:R$53
D4:D50	List	=Lookup!$O$2:$O$35
H3:H32	List	=Lookup!X$1:X$4

when I add rows the macro doesn't apply. Also weird not all of the previously shown information is visible after I run the macro (again). I apologize for being a pain in the tukus, but at a complete loss =( my whole sheet range will be A1:BJ102
Many thanks, RLBrown

 

[mumps]

Try:

Sub FillRange()
    Application.ScreenUpdating = False
    Dim PromoSt As Range, fnd As Range, LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Range("K3:BJ" & LastRow)
        .ClearContents
        .Interior.ColorIndex = xlNone
        .Font.ColorIndex = 2
    End With
    For Each PromoSt In Range("F3:F" & LastRow)
        Set fnd = Rows(2).Find(PromoSt, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            Cells(PromoSt.Row, fnd.Column) = PromoSt.Offset(, -2) & " " & PromoSt.Offset(, -1)
            With Cells(PromoSt.Row, fnd.Column).Resize(, PromoSt.Offset(, 1))
                .HorizontalAlignment = xlCenterAcrossSelection
                .Interior.ColorIndex = 3
            End With
        End If
    Next PromoSt
    Application.ScreenUpdating = True
End Sub

I have modified the code to clear any old data and colors.

 

[RLBrown]

Thank you, I replaced the old code and ran; received run-time error as follows. If you are at the end of your rope with me I completely understand.

Run-time error ‘1004:

Application-defined or object-defined error

**************************************************************

When ‘debug’ line 14 identified:

With Cells (PromoSt.Row, fnd.Column) .Resize(, PromoSt.Offset(, 1))

 

[mumps]

Please step through the code one line at a time. You can do this by placing the cursor anywhere in the code and pressing the F8 key. Each press of the key will execute one line of code. When this line of code is highlighted:

With Range("K3:BJ" & LastRow)

place the cursor over "LastRow" and you will see the value of that variable. Check your data to see if the number returned matches the last row with data in your sheet.

 

[RLBrown]

Yes, each instance of 'LastRow' is 102, the last row with data. The macro runs and all events (old & new) appear.
Still receiving runtime error each time the macro is run. Pasting image with highlight showing what clicking 'debug' shows.

If I click 'end' the process ends and new & old information appear --- I can press 'end' as part of the process and happily use this worksheet. But I also wanted to show/tell you what i'm seeing. Thanks

 

[mumps]

Click here to download your file. Please note that in column, rows 31 and 32, you have formulas. Therefore, I31 and I32, although they look blank, are not actually blank so the actual last row is not 30 but 32. Delete rows 31 and 32 and try the macro again. Also, please note that each time you run the macro, all the old information is deleted before being replaced with the new data.