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: 35

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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 Question
Center Across Selection Issue in Web App.
Replies
0
Views
678
Z01NK5
Z
M
  • Solved
Populate a cell with text and formatting based on a whether a date field falls within a date range
Replies
3
Views
198
Micron
M
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
219
Ludwig
L
D
  • Question Question
Average across multiple worksheets excluding unfilled cells
Replies
4
Views
220
Domenic
D
A
  • Solved
Conditional Formatting for rows of empty cells corresponding with dates between
Replies
4
Views
520
avril18
A

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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