VBA To Apply Borders Around Every Cell In Selected Range
Oct 19, 2012
I'm looking for the least amount of code to apply a border around every cell in a selected range. The standard With Selection approach is to go through six times for the sides, top, bottom, and insides, which results in a lot of code.
I attempted a single code line approach (Selection.Borders.LineStyle = xlContinuous), but I cannot seem to influence the color or thickness of the line. I'm not looking for fancy here...standard xlThin in black color is all I need.
View 3 Replies
ADVERTISEMENT
Jan 20, 2014
I'm using the code below to create sheets and copy pertinent data for each value found in column O on my data sheet.
Code:
Sub CreateSheets()
Dim WBO As Workbook
Dim ThisWS
[Code]....
What I'm now trying to do, is apply a formula for each cell, in columns C:N for each newly created sheet. The formula is 'The value of 100 minus the value of the cell'.
I've tried adding
Code:
cell.Value = 100 - cell.Value
to differing points of the script but I recieve a 'Type mismatch error.
View 2 Replies
View Related
Jul 6, 2009
i m trying to set up a macro to convert a range of user-highlighted(selected) cells to 3 significant figures: for example, convert 0.135564 to 0.136
the equation i found elsewhere online: ROUND(xx,3-(1+INT(LOG10(ABS(xx))))). but i can't quite figue out how to apply the equation to a selected range of cells via a macro.
View 5 Replies
View Related
Feb 24, 2014
I would like to group some columns to all the sheets that I will have selected. Unfortunately the below code only apply the code to the sheet I am looking at.
Code:
Sub Group()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws.Range("F:Q").Group
End With
Next ws
End Sub
View 3 Replies
View Related
Oct 2, 2009
For our attendance register, the master data table holds a list of all club members (one member per row).
The master data table consists of formula columns, data columns, and two data columns with drop-down list validation.
I'm using this
View 2 Replies
View Related
Nov 26, 2013
What I'm trying to do is put top&bottom borders on ALL cells in a range, not just the margins of the selected area. See spreadsheet below for explanation if required.
Surely there must be a way to do this through Excel menu, tools and whatever.
Alternatively, this could be solved if I knew how to select all rows in a spreadsheet (not Select All), because if I select rows one by one it will obviously apply the borders correctly. Is there a way to select multiple rows then?
View 5 Replies
View Related
Aug 16, 2006
With Range("A12", Range("E65536").End(xlUp))
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin......................
It is supposed to border data from columns A to E for all rows where data exists in column A. (like a dynamic range). How would I change the dynamic reference I have now?
View 5 Replies
View Related
Dec 4, 2009
This ia a recorded code to draw borders around cells on a given range
I am sure it can be shortened to 1-2 sentences!
Range("J11:O16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
View 9 Replies
View Related
Jul 12, 2009
I have a spread sheet I use to track tools. One tool may be checked out / returned several times a week. When I do custom sorting I end up with the Tools listed neatly by date and number. EX: ...
View 10 Replies
View Related
Jul 25, 2014
I am using this code to hide or unhide rows of text on another sheet:
VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)
Dim sAddress As String
Dim sValue As String
'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)
[Code]....
When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.
The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..
Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)
Code solution can be entered directly beneath:
VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True
View 1 Replies
View Related
Jun 20, 2006
I have designed a form with Grey horizontal borders.
When I print to my HP LaserJet 2420, the borders are black.
I have other forms with Grey borders where the borders print Grey.
I have not chosen to print Black and White in Page Setup.
How do I correct this?
View 6 Replies
View Related
Apr 2, 2014
i have data in range c10:h100
i want if select a cell e.g = c15 then
highlight the range c10:c100 with color
now highlight the all row only highlight defined range
View 5 Replies
View Related
Dec 23, 2008
I want to use the Worksheet SelectionChange event to update part of my worksheet/UI depending on what cell(s) the user has selected.
As an example, if I have two named ranges - $A$1:$B$10 and $C$1:$D$10 - I want to detect whether the user is in range 1, range 2, or neither, then update elements of the UI.
View 4 Replies
View Related
Nov 16, 2008
I have 12 named ranges on a sheet, ArrM1 thru ArrM12.
Each named range is same size, 6 Rows by 7 columns.
Q. If user selects a cell on sheet that is in one of these ranges, what is code to return name of range?
e.g. ArrM1 is cells c10:i15, user selects cell d12; I would like vba to return the name of the range "ArrM1".
Reason, I have the code below so if user selects cell in ArrM1 code is executed, but rather than reproduce code 12 times for each range I thought I could first see where user selects and then change variable for vrange,
Set vrange = wksYearlyCalendar.Range("ArrM1") to
Set vrange = wksYearlyCalendar.Range("ArrM" & x)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CalDaySel As String, CalDateSel As String
Dim vrange As Range
Dim cell As Range
View 9 Replies
View Related
Feb 15, 2010
I have a short macro to remove highlighting from certain fields when data is entered. Here is the full
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("j18:j500")) Is Nothing Then
Sheets("Internal Transfers").Unprotect Password:=""
With Target.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
Sheets("Internal Transfers").Protect Password:=""
End If
End Sub
The highlighting only goes away if you hit the Enter key after entering data in the cell. If you use the tab key after entering data, or use the mouse to select a new cell, the highlighting change doesn't occur. I have a feeling this might simply be related to the way I'm invoking this event. I've not worked with the worksheet_change event before, but I've read through various explanations and descriptions of this event and it isn't clear to me what I need to do.
View 2 Replies
View Related
Nov 10, 2013
I am having a hard time understanding why the following code (i am avoiding the select procedure on purpose) doesn't work:
Code:
Function APMS_Border(Namesheet As Variant, Mode As Variant, RowStart As Variant, ColStart As Variant, RowEnd As Variant, ColEnd As Variant, LeftBorder As Variant, TopBorder As Variant, RightBorder As Variant, BottomBorder As Variant, InterColor As Variant)
Dim BorderMatrix() As Variant
[Code]....
it only draws the left border of my range, am i missing something?
View 3 Replies
View Related
Jun 28, 2007
The code below is self explanatory but it does not undo the border related formatting. Can you please guide and help me figure out my mistake. The attached image shows the worksheet that I am using....
View 9 Replies
View Related
Jul 29, 2013
If Cell "B55" Cell is selected and I want to Select all the above rows to select till Row 1. Selected Row mught change Say B66 or B82 (Dynamic)
Whatever may be the Cell Selected, The result should be The above rows should get seleted. So that I want to delete the rows easily
HTML Code:
ws.Range("B55").Select
Range(ActiveCell, Activecell.Offset(-55,15)).Select
'This cannot work if the selected Cell is 65, So this should be Dynamic till above Range 1)
View 2 Replies
View Related
May 4, 2008
if a user has selected a range, how do you, in VBA, identify the:
1. Top left cell
2. Bottom left cell
3. Top right cell
4. Bottom right cell
For example if user has selected the range B5:M30, then we would want to identify in the macro:
1. Top left cell = B5
2. Bottom left cell = B30
3. Top right cell = M5
4. Bottom right cell = M30
View 9 Replies
View Related
Jan 16, 2010
If I've defined borders for a range of cells and then, changing my mind, change it to "No Borders", those cells are still different than the normal, non-formated cells. Is there a way to get back the default cell bounders?
View 3 Replies
View Related
Feb 9, 2010
I have a spreadsheet that I'm working on and I'd like to include a beforesave macro to format the spreadsheet. Basically I need a macro to select all rows with data in column 'a' and add a thick border to the top of the cells. Ideally the border would stop at column 'i' but that's not essential. I've attached an example of what I'd like. I've included 2 sheets 1 called 'before' and 1 called 'after'. They show what I'm after.
View 3 Replies
View Related
Sep 14, 2007
I have made a toggle button to turn diagonal borders on and off in a single cell. Is there a way to make the button transparent, such that the button can be placed over the cell itself and the contents of the cell will be visible underneath, no matter the condition of the toggle?
View 8 Replies
View Related
Jun 9, 2008
Is there a way to format any row in the workbook based on text value in VBA?
e.g. If I type "BLANK" in any row of a worksheet, then the certain cells in that particular row will Format Borders xlDiagonalUp (D:G,K).
View 5 Replies
View Related
May 2, 2013
I'm trying to use the below formula to to return "true" when text in cell (M2) matches text in selected cells on same row (A2, E2, and G2)
However, all formulas I found online that serve this purpose deal with cell range only, not predetermined cells with exclusions.
Formula:
=IF(ISNUMBER(MATCH(M2,A2:G2,0),TRUE,FALSE) (This will match all cells between A2 and G2 against M2)
reforming this formula to match selected cells only in-between that range (A2, E2 and G2)?
View 7 Replies
View Related
Jan 11, 2013
I created a named range formula:
=TEXT(TODAY(),"m/d/yyyy")&" "&TEXT(NOW(),"h:mm AM/PM")
The goal of that formula was to return a Date/Time stamp that is fixed and doesn't change over time. The formula is intended to run in which ever cell that I select to enter it into. I named the formula:
=DS
What I am trying to do is create a macro that will run the formula using a hot key function. I want to be able to select a cell and hit CTRL D and have that above formula run in that cell. I have multiple sheets in my workbook and I need the macro to be able to run on any sheet in my workbook. Below is what I have:
Sub DateStamp()
'
' DateStamp Macro
[Code]....
I was trying to apply the macro to only a certain range of cells on any given sheet.
View 1 Replies
View Related
Jun 21, 2014
I am looking to run two separate macros. I have a project plan and I am looking to be able to select a button whereby on-click, a new row is created within a selected cell. However if the cell clicked is outside of a pre-determined range, then the task is automatically entered at the bottom of the plan. I would like for the copied row to go ABOVE the selected cell and have all of the same formatting as the row below (not the top - as is default in Excel).
The second is going to be very similar but will copy a task category (like a header item) and the first task (row) found below. It will also be copied from below and be inserted above the selected cell.Both macros will clear certain cells, whilst maintaining the contents of others, with formula contained. I.E. Columns C,D,E,H,I,J will be cleared.
View 2 Replies
View Related
Feb 22, 2009
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this.
The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got
'FindRemove = lstRemove.Value
'If FindRemove = "" Then End
' Goes to the start of the Data column
'Sheets("Staff").Select
'Range("B4").Select
' Tests current cell against FindRemove
'Do
'If ActiveCell.Value = FindRemove Then
'Call CopyPerson
'Exit Sub
'Else: ActiveCell.Offset(1, 0).Select
'End If
'Loop Until ActiveCell.Value = ""
'End Sub
View 9 Replies
View Related
Mar 21, 2009
I am carrying out some sensitivity testing on a model, and would like to greatly increase the speed of the process by being able to call in from a user defined cell a named range, which is then pasted to the right of the cell.
The attached file steps through the process I have in mind.
View 9 Replies
View Related
Jan 22, 2010
Is there anyway to apply the $ to cell references in formulas across a range of cells? For example, I currently have the following in Column A:
=AAA_SR_F_1!$G5
=AAA_SR_F_1!$G6
=AAA_SR_F_1!$G7
And the following for Column B:
=BBB_SR_F_1!$G5
=BBB_SR_F_1!$G6
=BBB_SR_F_1!$G7
Is there anyway to apply the $ to the entire range listed, i.e. AAA_SR_F_!!$G$5, etc.
View 2 Replies
View Related
Nov 8, 2007
How do I change this formula to represent a range? I want cells C2:C18, but I ran out of arguments - can't add any more. If DRC or Production shows in the column, I want the word "Outstanding" if not, I want the word "Completed" .....
View 9 Replies
View Related
