Auto-formatting Rows With ILastRow Variable
Feb 14, 2007I need each row in the Range("A2:G" & iLastRow) to be formatted with the top and bottom border. I'd prefer to go loopless but if that's not possible I will use the loop.
View 2 Replies
ADVERTISEMENT
Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions
Aug 8, 2009On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies View RelatedCan Auto-hide Rows Or Auto-set Row Height
Jun 27, 2014I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell?
Can Auto-Hide Rows Or Auto-Set Row Height?
Jun 27, 2014I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell? There is only one column of data.
Variable Auto Sum
Oct 3, 2008Firstly not really sure if this problem can be solved by a custom formula or if it will require VBA code to resolve it.
I have multiple instances of blocks of cells throughout a worksheet which require a total of that section. Very simple individually (see below) but becomes a nighmare when you need to insert a new row or amend the formulas; which can only be done manually.
A B
1
2 10
3 5
4 10
5 7
6 TOTAL 32
So in this instance the total would be obtained by using sum(B2:B5).
The number of rows in each section to be totalled can vary from 1 row to say 50 rows; however the common factor is that there is always a blank row above the first row of figures.
In essence, I am looking for a single custom formula that can be used anywhere in the worksheet which says:
"Sum all cells in this column, starting with the row above the total, counting upwards and stopping when it reaches a blank (empty) cell"
I know that I can use OFFSET in conjunction with SUM to allow for variable totals when you insert a new row but it still has to have specific column and row references.
I am guessing that this has been asked before but nothing came up when I tried to search the forums for a similar problem.
Auto Increment Based On ID Variable
Oct 3, 2013I have a following the following dataset:
ID YearWk Sales
1 201310 $1000
1 201311 $1200
1 201312 $1300
1 201313 $1400
2 201310 $2000
2 201311 $2200
2 201312 $2300
2 201313 $2400
I'm trying to figure out how to create a new variable that will increment and reset based on ID.. similar to the FIRST. function in SAS.
Final Dataset:
ID YearWk Sales Count
1 201310 $1000 1
1 201311 $1200 2
1 201312 $1300 3
1 201313 $1400 4
2 201310 $2000 1
2 201311 $2200 2
2 201312 $2300 3
2 201313 $2400 4
Change One Variable When Dragging Down A Column To Auto-complete?
Feb 25, 2013How do you drag down a column to auto complete the values but do it so it only changes one variable.For example, I am dragging down =IF(ROW()<=$B$5,MOD(E2*B1,B2),"") down my column.
However, I only want the "E2" value to change, and I want B1 and B2 to stay that way. Whenever I drag down, it keeps changing all three values. Is there a way to make it just so that the E2 value is the only one that changes?
Auto Time Formatting
May 28, 2009Hears a quick question..
Is there away in VB that a cell can be automatically set to time
What i mean at the moment when i need to type in a time i have to type it in as follows 10:00 ( the cell is formatted to time)
What i was wondering is there a way of programming in VB so that when the user types in 1645 it automatically changes it to 16:45
AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS
Mar 23, 2009I RECORDED THIS MACRO BUT I WILL LIKE TO MAKE TO AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS MY CODE
Sub FORMAT_AS_A_TABLE()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$1900"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleDark5"
End Sub
Auto-formatting A Text String.
Oct 15, 2008I have a cell containing a post code (U.K). For example CF678JU however I need it to be formatted to show CF67 8JU. Is there a quick option of setting a formatting rule to add the space instead of me going into each cell and changing... (there's thousands of them!!!)
View 2 Replies View RelatedAuto Filling Conditional Formatting?
Apr 9, 2013I have created a couple of conditional formatting rules on the first row that fill the whole row of the table based on whether there a number in one cell or another. (Fill the whole row green if cell E3 has a number in it, or fill the whole row red if Cell F3 has a number in it).
I want to apply this to each row of the sheet but when I auto fill it down, the conditional formats fill but the reference cells stay as E3 and F3. This is causing the entire table to be filled instead of just the individual rows.
I don't want to have to put in conditional formatting rules manually for each row. Is there a way to auto fill down with the reference cells changing to suit each row like a normal formula would?
Long Date Auto Formatting?
Dec 8, 2011for some reason every number in excel is automatically formatting as long date. while i can go into the individual cells, columns, rows, sheets and change the format it always auto formats to date at the outset.
View 7 Replies View RelatedAuto Generate A Comment Box Based On A Cell Value That Has Variable Reference Cells
Jan 30, 2010I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B".
If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C".
Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
Conditional Formatting To Auto Fill Cell
Apr 23, 2009I have written in some conditional formatting to have a cells automatically updated based on a response in a cell above. That is: If a no response is chosen to a certain question, then all other questions in that range are to also be a No response. I have attached the document im working on with an outline of what the problem is!!!!
View 4 Replies View RelatedAuto Insert Row Before Monday With Formula And Formatting
Jun 21, 2014In column H I have a list of dates as I will add new rows each day for different records - it's a payment collection. e.g.
18/06/14
18/06/14
19/06/14
19/06/14
19/06/14
21/06/14
23/06/14
21/06/14 is Saturday & 23/06/14 is Monday, Sunday is holiday so that day is no collection but I want Sunday row also be there with date in that column. Currently I am doing it manually.
Is there a method with VBA (or other) to automatically insert a row with formulas & formatting above Monday date after workbook save.
I have data validations, conditional formatting & lot of formulas there. I have other date column also on workbook but I want it happens only with column with H. Also what if I have to change column H in future.
Stop/Prevent Web Query Auto Formatting
Apr 23, 2009I use a query table to import data into a workbook which works fine
With wsActiveSheet.QueryTables.Add(Connection:=sQueryString, _
Destination:=wsActiveSheet. Range("A1"))
.Name = CStr("main.html?LANG=de&search=true&searchterm=" & sIdentifier & "_1")
.PreserveFormatting = True
.BackgroundQuery = False
.RefreshStyle = xlInsertEntireRows 'xlInsertDeleteCells
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebTables = "9,14"
.WebPreFormattedTextToColumns = True
.WebSingleBlockTextImport = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones).
Unfortunately excel always converts this field into a date and a time but this is not what I want.
Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before
Applying Conditional Formatting On Auto Shapes
Mar 7, 2006I have many autoshapes in my excel file and I want to use the "VBA version of conditional formatting" one two of these autoshapes. I am saying VBA version because conditional formatting does not work on autoshapes (just found that out). The autoshapes on which I want the conditional formatting to work are "AutoShape 73" and "AutoShape 74" (from Autoshapes > Stars & Banners > Explosion1).
This is how I want the conditional formatting to work:
If the text in the autoshape is "A", fill the autoshape with RED color and bold + white arial 12 font
If the text in the autoshape is "B", fill the autoshape with BLUE color and white arial 12 font
If the text in the autoshape is "C", fill the autoshape with GREEN color and bold + black arial 12 font
Using Text Variable For Conditional Formatting
Mar 11, 2014I'm trying to reverse engineer a spreadsheet (see attached) and apply the data table to my own spreadsheet. The inspiration spreadsheet (attached) utilizes a 2 variable data table and conditional formatting, where the cells are formatted based on their value in relation to a variable called "Gross". So the rules look something like this:
Cell Value Between 0 and Gross-1
Cell Value > Gross
Cell Value = Gross
Cell Value < 0
Graded Color Scale Minimum ="<385000" Maximum =">385000"
I assume the Gross is calculated as 25% * 350,000 = 87,500. The hard coded value of 385000 is actually incorrect, because it's based on a percentage of 10%... but I'll let that slide The resulting formatting of the data table would support the hypothesis that "Gross" = 87,500. However, when I reset the Conditional Formatting to reference another cell with the calculation result of 87,500 it completely throws off the formatting. I'm simply stumped as to how they are using the text "Gross" as a variable. How/where are such variables set so they can be used in conditional formatting?
How to apply the data table logic itself to another spreadsheet.
Conditional Formatting, Variable Text
Mar 1, 2010I can't figure out how to set up conditional formatting to let me know if the value of a text cell is NOT X or Y. In other words, the only appropriate responses are X or Y (or Y or N or whatever the case may be) and if it's NOT one of those two letters, I want to apply conditional
formatting. Also, will it accept either upper or lower case (I'm fine with it either way).
How To Avoid Auto-formatting To Date Format In Excel
Apr 6, 2014System is generating IDs like 1-OCTO33, 1-NOVE44, which on exporting in excel converts to 1-OCT-33 & 1-NOV-44. How can this auto-formatting be restricted in excel?
To test, you can try entering in excel 1-OCTO33, it converts to 1-OCT-33.
I am looking for some setting in excel, which would prevent it on exporting to excel. Other options like changing column format to text or using a limiter ' will not work in this case.
Stop Auto-Formatting Headers In Table Upon Row Insert
Jan 3, 2013I decided to format my data as a table so that excel will auto-fill my formulas when inserting new rows which works quite well. I have one snag though, when trying to insert a new row at the very top (i.e. inbetween the header and first row) and choosing Format As Below, it also applies Data Validation and Conditional Formatting to the header. The inserted row, however, is actually formatted fine and works well.
How do I stop this from happening, and why would Excel do this anyway (as it is effectively applying formatting to TWO rows when only one row is being inserted)?
Conditional Formatting Of Range Of Variable Length
Apr 30, 2009I have an issue regarding use of conditional formatting where the range i wish to format is on variable length.
For example; lets say i want to format F15 downwards depending on there being a value in the cell, how could I do that ?
Lets say I want the cell to turn GREY if number is 0, RED if the number is below 85, GREEN if above 95 and AMBER otherwise.
How could I do that ?
Any how could i ensure it picks all rows in the range where numbers have been added
Macro To Add Conditional Formatting To Variable Range
Jun 16, 2008I have 2 columns 'C' and 'D' which I want to apply Conditional Formatting to (i.e. colour the background of the cell in column 'D' for the respective row in colum 'C') if they contain different values. I have the following
Sub CellCCondFormatting()
Dim j As Long
Range("C2").Select
j = Range("C2"). CurrentRegion.Rows.Count
MsgBox ("1st: " & j)
Range("C2:C" & j).FormatConditions.Delete
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(1).Interior.ColorIndex = 3
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(2).Interior.ColorIndex = 4
MsgBox ("2nd pass: " & j)
End Sub
It iterates through all rows in my CurrentRegion OK but the Conditional Formatting 'formula' operates on the wrong value in column 'D'. For example, when viewed via menu option Format > Conditional Formatting... row 2 column 'D's Conditional Formatting value is D1714, row 3 column 'D's value is 'D1715' and so on....................
Using Conditional Formatting To Fill Certain Cells Based On Variable?
May 23, 2013The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.
View 2 Replies View RelatedReferencing Variable In Conditional Formatting But Applies To All Excel?
Jul 9, 2012I have some VBA code that formats my sheets and works fine, but the issue is that I run this same piece of code more than once in different sheets with the only difference being the formula references a different cell.
' Green Cells indicate that the supervision is within the year
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=Datedif(D8,today(),""m"")
Macro To Add Conditional Formatting Code To Variable Range
Jun 12, 2008I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.
View 2 Replies View RelatedAuto Hide The Rows
Oct 22, 2008I want to autohide any row that does not have a number in the Current # of Bins column.
View 2 Replies View RelatedAuto Numbering Rows
Jan 4, 2007I have a requirement where, in one of the column i would like to have an auto numbering (similar to Microsoft access). I know this can be done using Macros, but is there any other better alternative.
View 4 Replies View RelatedAuto Color Shading Of Rows
Oct 24, 2005Can I automate an excel worksheet such that if ,say, "436" is written in a
certain cell it shades that row yellow, or if "437" is in that cell it
shades the row blue. It would then be easy to visually tell in an excel
chart which lines belonged to ,say, specific companies.
Auto Hiding Rows With Zero Values
Apr 6, 2014I'm using the following code to hide rows with zero values in my workbook. I have 10 sheets for potential data/products and a "summary" sheet that is set up to pull all the information from the 10 sheets. I often don't use all 10 sheets so there are a lot of blank cells. I set up the code below to hide zero values to make the "summary" sheet easier to work with. I am encountering a problem with cells that are formatted as dates. a zero value/empty cell shows up as 1/0/1900 and is therefore not auto hidden.
Either any adjustment to my formatting or code to automatically hide these blank date cells on the "summary" sheet?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)Dim c As Range
For Each c In Range("C3:C8778")
If c.Value = "0" Then
c.EntireRow.Hidden = True
[Code] ...........