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

R

RLBrown

New Member
Joined
Aug 6, 2018
Messages
25
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.
 

Attachments

  • Screenshot 2022-02-16 142513.jpg
    Screenshot 2022-02-16 142513.jpg
    101.5 KB · Views: 15

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
If you want to center across selection, you will need a macro. It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
 
Upvote 0
mumps said:
If you want to center across selection, you will need a macro. It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
Click to expand...
Will have to get my IT dept. to install the add-in, we're fairly locked down on what we can do. Once they install I will add. Thank You.
 
Upvote 0
Unfortunately, that didn't work properly. First select the entire used range in your sheet and in the add-in click click "Mini Sheet". The paste here.
 
Upvote 0
Current:
PeriodGroupBrandItemPromo TypePromo
Start		# of
Weeks		Promo
Price		P1 WK1P1 WK2P1 WK3P1 WK4P2 WK1P2 WK2P2 WK3P2 WK4P3 WK1P3 WK2P3 WK3P3 WK4P4 WK1P4 WK2P4 WK3P4 WK4P5 WK1P5 WK2P5 WK3P5 WK4P6 WK1P6 WK2P6 WK3P6 WK4
P2PlanksPremoWidgetBOGOP2 WK34$0.79Widget BOGOWidget BOGOWidget BOGOWidget BOGO
P3BoardsPremoSaucer4/$5P3 WK312$0.99Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5
Goal:
PeriodGroupBrandItemPromo TypePromo
Start		# of
Weeks		Promo
Price		P1 WK1P1 WK2P1 WK3P1 WK4P2 WK1P2 WK2P2 WK3P2 WK4P3 WK1P3 WK2P3 WK3P3 WK4P4 WK1P4 WK2P4 WK3P4 WK4P5 WK1P5 WK2P5 WK3P5 WK4P6 WK1P6 WK2P6 WK3P6 WK4
P2PlanksPremoWidgetBOGOP2 WK34$0.79
Widget BOGO​
P3BoardsPremoSaucer4/$5P3 WK312$0.99
Saucer 4/$5​
 
Upvote 0
mumps said:
Unfortunately, that didn't work properly. First select the entire used range in your sheet and in the add-in click click "Mini Sheet". The paste here.
Click to expand...
Hi mumps, I pasted it from XL2bb --- hope it works this time. Thank You!!
 
Upvote 0
Current:
PeriodGroupBrandItemPromo TypePromo
Start		# of
Weeks		Promo
Price		P1 WK1P1 WK2P1 WK3P1 WK4P2 WK1P2 WK2P2 WK3P2 WK4P3 WK1P3 WK2P3 WK3P3 WK4P4 WK1P4 WK2P4 WK3P4 WK4P5 WK1P5 WK2P5 WK3P5 WK4P6 WK1P6 WK2P6 WK3P6 WK4
P2PlanksPremoWidgetBOGOP2 WK34$0.79Widget BOGOWidget BOGOWidget BOGOWidget BOGO
P3BoardsPremoSaucer4/$5P3 WK312$0.99Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5Saucer 4/$5
Goal:
PeriodGroupBrandItemPromo TypePromo
Start		# of
Weeks		Promo
Price		P1 WK1P1 WK2P1 WK3P1 WK4P2 WK1P2 WK2P2 WK3P2 WK4P3 WK1P3 WK2P3 WK3P3 WK4P4 WK1P4 WK2P4 WK3P4 WK4P5 WK1P5 WK2P5 WK3P5 WK4P6 WK1P6 WK2P6 WK3P6 WK4
P2PlanksPremoWidgetBOGOP2 WK34$0.79
Widget BOGO​
P3BoardsPremoSaucer4/$5P3 WK312$0.99
Saucer 4/$5​
 
Upvote 0
Try this macro:
VBA Code: 
Sub FillRange()
    Application.ScreenUpdating = False
    Dim PromoSt As Range, fnd As Range
    For Each PromoSt In Range("F2", Range("F" & Rows.Count).End(xlUp))
        Set fnd = Rows(1).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
This is what the result looks like. The XL2BB addin doesn't display the center across selection for some reason but when you run the macro, it will be displayed in your sheet.
PeriodGroupBrandItemPromo TypePromo Start# of WeeksPromo PriceP1 WK1P1 WK2P1 WK3P1 WK4P2 WK1P2 WK2P2 WK3P2 WK4P3 WK1P3 WK2P3 WK3P3 WK4P4 WK1P4 WK2P4 WK3P4 WK4P5 WK1P5 WK2P5 WK3P5 WK4P6 WK1P6 WK2P6 WK3P6 WK4
P2PlanksPremoWidgetBOGOP2 WK34$0.79Widget BOGO
P3BoardsPremoSaucer4/$5P3 WK312$0.99Saucer 4/$5

Please note that I have unmerged all the merged cells and so some of the row locations have changed. My sample starts in row 1 and ends in row 3. Make sure your actual workbook data is organized in the same way. You should avoid using merged cells at all cost because they almost always create problems for macros.
 
Last edited:
Upvote 0
Oh great, wise, mumps... I can't thank you enough, your code is a thing of beauty. I dont understand it but it works and will take my promo tracker to the next level. The words sound small, but you have my sincere appreciation & gratitude. -- rlbrown
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question
Center Across Selection Issue in Web App.
Replies
0
Views
98
Z01NK5
Z
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
376
Legacy 352679
L
S
  • Solved
Inserting Picture (macro) - size and position
Replies
3
Views
439
Domenic
D
C
  • Solved
Formula Help (Calendar) Expanded
2
Replies
15
Views
691
Eric W
Eric W
Y
  • Question
How to have a formula auto update based off the number of filled rows.
Replies
0
Views
190
Yaeger38
Y

Forum statistics

Threads
1,214,605
Messages
6,120,476
Members
448,967
Latest member
visheshkotha

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top