Add cell borders based on real-life date

StavrosWTF

New Member
Joined
Nov 18, 2016
Messages
3
So I want to automate some things in my excel. So what I want is for excel to automatically add borders to specific cells based on real-life date. So this is how my sheet is.
Week Number
Week 1
Week 2
Week 3
Week 4
1,2,3,4...

<tbody>
</tbody>
So for example, let's say A2 cell's value changes to 2, then I would want the whole Week 2 column to have a thick black outside borders. However, when A2 cell's value changes to 3, I want Excel to remove the borders from Week 2 column and then add borders to Week 3 and so on... I hope you can help me with this please.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
I might be misunderstanding you, but you could just add the following to each cells conditional formatting (use formula) assuming week 1 is in cell C2

=A1=C2 - added to week 1's condirional formatting equation field
=A1=D2 - added to week 2's
=A1=E2 - week 3
=A1=F2 - week 4

Hope this helps
 
Upvote 0
This will do it:
Code:
' Border Macro' Border by week 1 - 5, in columns B-F, data is on row 2.
' Keyboard Shortcut: Ctrl+j
'
    Dim theClmn As Integer
    Dim theWeek As Integer
    Let theWeek = Cells(2, 1)
    Let theClmn = theWeek + 1
    
    Range("B2:F2").Select
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    Cells(2, theClmn).Select
    
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A2").Select
 
Upvote 0
This will do it:
Code:
' Border Macro' Border by week 1 - 5, in columns B-F, data is on row 2.
' Keyboard Shortcut: Ctrl+j
'
    Dim theClmn As Integer
    Dim theWeek As Integer
    Let theWeek = Cells(2, 1)
    Let theClmn = theWeek + 1
    
    Range("B2:F2").Select
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    Cells(2, theClmn).Select
    
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A2").Select
I want you to explain me what things I should put in these depending on my sheet (cause this i posted was a quick example):
data is on row 2 (What if i have 5 more rows where the data is?)
Let theWeek = Cells(2, 1) (What does this do?)
Let theClmn = theWeek + 1 (What does this do?)
Cells(2, theClmn).Select (What does this do?)
 
Upvote 0
I will answer your questions in the body of the macro, with comments which I should have included in the first place.

Code:
[COLOR=#333333][I]' Border Macro' Border by week 1 - 5, in columns B-F, data is on row 2.[/I][/COLOR]' Keyboard Shortcut: Ctrl+j
'
    Dim theClmn As Integer
'   Dim theRow  As Integer   'to ve varied in a loop to control multiple rows.
    Dim theWeek As Integer

    Let theWeek = Cells(2, 1)    ' this is where week is defined
    Let theClmn = theWeek + 1   ' column offset by one because the week is in row two col one.
    
    Range("B2:F2").Select       ' change this to F6 if there are five rows of data beginning at 2.    
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone   ' these lines delete the old borders, if any.
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
'   insert a loop here, varying theRow replacing the literal row "2".
'   For theRow = 2 to 6

'   if week is posted to multiple rows, vary the column per each.
'      thus L theClmn = cells(theRow, cells(theRow,1) + 1)
'   Change the next line by replacing the 2 by theRow

    Cells(2, theClmn).Select
    
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
'   Next theRow                closing the for loop.

     [COLOR=#333333][I]    Range("A2").Select[/I][/COLOR]

Let me know your preferences and I will be happy to take the macro to completion.
 
Upvote 0
This should be easy to do with conditional formatting. Share a clearer description of the layout and the data and someone should be able to assist you.
So I want to automate some things in my excel. So what I want is for excel to automatically add borders to specific cells based on real-life date. So this is how my sheet is.
Week Number
Week 1
Week 2
Week 3
Week 4
1,2,3,4...

<tbody>
</tbody>
So for example, let's say A2 cell's value changes to 2, then I would want the whole Week 2 column to have a thick black outside borders. However, when A2 cell's value changes to 3, I want Excel to remove the borders from Week 2 column and then add borders to Week 3 and so on... I hope you can help me with this please.
 
Upvote 0
This should be easy to do with conditional formatting. Share a clearer description of the layout and the data and someone should be able to assist you.
This is my file: http://www.filedropper.com/transformationlog
So let me explain. In Cell B3 I have the number of the current week. From Column A6 to AB6 and rows from 6 to 12 I have the week contents (plus the other 2 duplicates of this in the first sheet). So what I want is let's say B3 has a value of 3, then I would want those cells to have a black thich outside border: D6-D12, D14-D25, D27-D36.
 
Upvote 0
You don't give the file's location, only the name of the repository. At this point, we really need to see the file under question.

In the meantime it will be time well spent to look into conditional formatting as the solution.
 
Upvote 0
Discovered that conditional formatting cell border format has limited choices (the thick borders are missing).

Other than that, create 3 rules (adjust the columns and rows as desired):
1) This is for the top row
applies to =$D$6:$G$6,$D$14:$G$14,$D$27:$G$27
Select 'Use a formula to determine which cells to format'
Formula is =COLUMN(D3)-1=$B$3
Format borders to have left, top and right borders.

2) This is for the rows other than the top and bottom
applies to =$D$7:$G$11,$D$15:$G$24,$D$28:$G$35
Select 'Use a formula to determine which cells to format'
Formula is =COLUMN(D3)-1=$B$3
Format borders to have left and right borders.

3) This is for the bottom row
applies to =$D$12:$G$12,$D$25:$G$25,$D$36:$G$36
Select 'Use a formula to determine which cells to format'
Formula is =COLUMN(D3)-1=$B$3
Format borders to have left, bottom, and right borders.

This is my file: http://www.filedropper.com/transformationlog
So let me explain. In Cell B3 I have the number of the current week. From Column A6 to AB6 and rows from 6 to 12 I have the week contents (plus the other 2 duplicates of this in the first sheet). So what I want is let's say B3 has a value of 3, then I would want those cells to have a black thich outside border: D6-D12, D14-D25, D27-D36.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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