Automatically Format Cells On Enter

Nov 22, 2006

I'm trying to write a macro that automatically italicize the a range of cells when you select a cell, and de-italicize it if you click it again. In this example, when select B12, the macro will automatically select B12:H12 and italize them. And if you select B12 again, it will automatically select B12:H12 and de-italize it.

This is what I have so far:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyAddress = ActiveCell.Row
If MyAddress > 11 And MyAddress < 159 Then
If Not Intersect(Columns(2), Target) Is Nothing Then
Intersect(Columns(2), Target).Resize(, 7).Select
End If

Select Case Target.Font.Italic
Case "True"
Target.Font.Italic = False
Case "False"
Target.Font.Italic = True
End Select
End If
End Sub

The code above does not work correctly.

View 4 Replies


ADVERTISEMENT

Custom Number Format - Automatically Enter Dashes At Appropriate Spot

May 7, 2014

I am needing a custom format for the following. I need a cell entry to be 1 letter, three numbers, a dash, 5 numbers, a dash, two numbers, a dash, and then two number. Example: A109-54785-13-00. The first letter will almost always be an "A."

To further complicate the matter, the entry is copied from an email and pasted without the dashes. In the above example, the number in the email would read, "A109547851300." I copy it from the email and paste it in the proper cell in the worksheet. I would like the custom format to automatically enter the dashes at the appropriate spot.

If it was all numbers, this would be easy. I created a custom format for the numbers, but when the A is included, Excel no longer treats it as a number and the custom format did not work.

Is this possible in Excel (without VBA).

I could use the custom number format I created and then later go back and add the "A" at the beginning, but that is as much work as manually adding the dashes.

View 2 Replies View Related

How To Automatically Enter A Value Next In Sequence In A Cells

Dec 18, 2012

I was wondering how I could automatically enter a value next in a sequence in a cell when an adjacent cell has data in it and continue doing so indefinitely (or until the 65536th row). I could do this with autofil, but The sequence starts in the middle of the worksheet (there is a title and document data in the top few rows). The layout is as follows:

Cell A17 has "Part 1" in it (and always will before the form gets filled out), and all cells in colum A beneath that will be blank. Column B is where the user enters a dimension. So, at the start, entering a value into cell B17 won't do anything. However, if they enter a value into cell B18, I would like cell A18 to automatically be filled with the next part number in the sequence (in this case, "Part 2" in cell A18). The user will never skip rows when they enter data so the sequence will always be +1 to the previous cell in the column.

I'm doing this so they can just print out the completed sheet and not have to edit out the empty "Part *" cells that have no corrosponding dimensions.

Is there a simple way to tailor autofill to do this or would I need a macro?

View 3 Replies View Related

F2 + ENTER To Format Cells

Dec 31, 2009

I have some imported data that Excel does not recognize as date or time so I have to edit each cell pressing F2 and the enter to "convert" the value.

I have about 120.000 records and the macro takes long time to do the job.

View 8 Replies View Related

Automatically Enter Cell Value Based On The Values Of Two Other Cells

Jan 8, 2010

I am working on blood pressures. I want cell C1 to place either, "Prehypertension","Stage I Hypertension" or, "Stage II Hypertension" depending on the values of cells A1 or B1 ....

View 7 Replies View Related

Information To Enter The Tables And Then Automatically Move Into The Next Available Cells Below

Sep 23, 2008

I am trying to use visual basic editor in excel. I have all ready set up my user form where information can be entered, but I ave having trouble getting the information that is entered in the user form into the correct cells in excel.

I am wanting my information to enter the tables and then automatically move into the next available cells below.

Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("details of cars in stock")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
If Trim(Me.txtreg.Value) = "" Then
Me.txtreg.SetFocus
MsgBox "please enter a registraion number"
Exit Sub
End If
ws.Cells(iRow, 1).Value = Me.txtreg.Value
Me.txtreg.Value = ""

View 9 Replies View Related

Automatically Format Range Based On Formula Result Of Cells

May 16, 2009

The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:

1) To replace the code provided below using the calculation event
2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement
3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible
4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean
Set d = Intersect(Range("I:I"), Target)
If d Is Nothing Then Exit Sub
For Each c In d
Select Case UCase(c)
Case "C"
fc = 1: fb = True: bc = 4
Case "O"
fc = 2: fb = True: bc = 3
Case "D"
fc = 2: fb = True: bc = 46
Case "G"
fc = 2: fb = True: bc = 5..................

View 4 Replies View Related

Macro Required To Conditionally Format Cells Automatically, Responce To User Events

May 2, 2008

ABCDEFGHI need to create a macro to perform some conditional formatting1DateDayTypeTimeConfirmed Order No.Site Location2SHEET OVERVIEW301/01/08TuesdayAB4CVertically there will be 366 tables to represent 366 days and Horizontally 10 tables to represent 10 employees,5Dwhich enables the work activities of 10 employees to assigned over the period of a year.6E7AAFIn the type box marked 'A' and 'AA' the user selects from a drop down box a parameter as list below left.8GOn entry of an 'n' in the type box the user can enter data into the 'time','confirmed order','site location' boxes.9HThe idea of having 2 x type boxes is to allow the day to be split into morning and afternoon.10I1102/01/08WednesdayMACRO / VBA FUNCTIONALLITY REQUIRED 1213In the 'type' boxes if the user selects anything other than 'n' then the 4 'site location' boxes and coloured and 14the appropriate text from the list below left is inserted into the 4 associated 'site location' boxes.1516Example:1718Box marked 'A' the user selects 'h'. Boxes marked 'BCDE' and filled with colour and the text holidayBox marked 'AA' the user selects 't'. Boxes marked 'FGHI' and filled with colour and the text trainingType List Entry ParametersWhen the user selects type 'n' after the associated boxes are returned to there blank state (no colour or text)nnormal dayhholidayThe macro/vba would have to respond on everytime a 'type' box changesssickttrainingThere would be 2 x type boxes per day, 366 days a year and for 10 employees. Therefore it would have to monitoruunauthorised absence2 x 366 x 10 (7320) type boxesbbank holidayccompany shutdown

View 9 Replies View Related

Automatically Insert New Row When Hit Enter?

Mar 23, 2012

I am trying to keep my checkbook in excel and want to make a spreadsheet that inserts a row every time I hit enter. That way my most reason transaction and balance is always at the top rather than the bottom of the spreadsheet... The inserted row should have the same formulas as the row below.

View 7 Replies View Related

Automatically Add The Numbers As I Enter Them

Apr 25, 2007

How do I get a cell to automatically add the numbers as I enter them,for example. I want to keep track of my gas bills for income tax. So if I go in each day to add that days gas amount I want that cell to auto add each entry for me, rather that constantly add my bills and then re-enter every time I enter new amounts.

View 9 Replies View Related

Automatically Add To Another Cell Upon Enter

Nov 10, 2006

I would like to place a value of like say 100 dollars if the cell next to it is populated with any text for example: customer then 100.00 so if I type anything the cell, the next cell populates a specified value

View 5 Replies View Related

Automatically Enter Password

Apr 3, 2007

I have recently written some programming in a spreadsheet (1) which, on opening, opens up another spreadheet(2), ammends this spreadsheet(2), and then closes it before the user can start using the original one(1). This is done to monitor the usage. The information added into the atomatically opened spreadsheet(2) includes date and time of opening and the users id as well as adding 1 to counter cells. Both .xls files are located on the same server and I dont want people to catch on to the monitoring and if they do I dont want them to then go into this file(2) and remove their information.

If I password protect this file(2) it then promts to enter a password when it runs at the startup of the initial spreadsheet(1) which is no good as it would defeat the purpose if the user knew the password. Is it possible to add a few lines into the code (see below) after it initiates the opening of the usage file?

Private Sub workbook_open()
Application. ScreenUpdating = False
Workbooks.Open Filename:= _
"S:Newman CommonCATEGORY MANAGEMENT - RangesEPoS Usage.xls"
Dim Counter As Integer
Counter = Cells(2, 7)
Counter = Counter + 1
Cells(2, 7) = Counter
Cells(Rows.Count, "BV").End(xlUp).Offset(1, 0) = Now
Cells(Rows.Count, "BW").End(xlUp).Offset(1, 0) = Application.UserName
ActiveWorkbook.Save
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub

View 6 Replies View Related

Automatically Enter Logon Credentials Using VBA

Oct 8, 2013

I have a command button that opens 5 windows when it is selected. Each window asks us to enter the username, password and logon to in order to sign on...thus, we have to do this a total of 5x manually.

If possible, I'd like for the aforementioned fields to be entered using VBA code, so when I click the command button, all 5 windows open with the login infomration already entered.

For examples, sake, lets say the usernmae is ABC1234, the password is Password and the Logon To field is Alliance. If this is possible, how would I code this?

View 2 Replies View Related

Automatically Enter To Next Row Once The Typing Has Reach The End

Apr 21, 2009

Is there a way that Excel can automatically enter to next row once the typing has reach the end.

Example: I have 5 columns (A to E) and I am typing at column A. While typing, the text will go along to cell B, C, D and E. The problem is, if I don't manually go to next row and continue typing, the text will go to column F, G and so on. Is there a way where excel can automatically jump to next row if the text has reach column E?

Another problem is, say I have 3 rows full of text (column A to E). If I edit one of the row to exter some new texts, the whole sentence will go along to column F, G and so on. What I can do now is, re-edit all the rows to adjust them back.

View 9 Replies View Related

Time Formatting - Automatically Enter Pm In Column

Nov 29, 2013

How to set a column up to show it as the time of day but unless I enter PM after I put the time in the column it always comes up as AM.

Is there some way to just enter 9:09 in column d and or f and have it show up as pm? When I enter 8:10 in column b it automatically adds the AM to it but if I do the same in the column for the night and I do not type in pm it will automatically enter it as am.

I have attached the workbook and did delete the macro - which was just to clear all of my entries but when I click to open it again I am still getting the message about the macro.

In the workbook when I enter the time in column B I can just type in 7:14 and it will automatically add the AM to it. However, when I get to column D or F if I just type in 9:09 or whatever time it is in the evening it will automatically add AM to it unless I type 9:09 pm.

Is there some way to set it up so that I can just enter the time and it will automatically enter pm for everything in that column?

2013 sugar logs.xls‎

View 9 Replies View Related

How To Make Cell Enter Text Automatically

Jan 6, 2014

In column B I will be entering a number and I would like it to automatically enter gallons in column c when i do so. So if i enter 356 in b2 i want it to say gallons in c2.

View 1 Replies View Related

Enter The Pointer Automatically Jumped To Cell “H”

Jan 14, 2009

See attached sample sheet for more detail. I used code for show date and user id in cell “H” and “I”, but my problem is when ever I write some thing between cells “A” to “G” and enter the pointer automatically jumped to cell “H”, I want when I enter the pointer should be stay next cell.

View 2 Replies View Related

Automatically Add Rows To End Of Spreadsheet By Pushing Enter

Jun 1, 2006

I am making a template for my company to automatically calculate the amount of sheet metal needed for a specific job. The spreadsheet could get very long depending on how much duct is needed. Is there any way I can automatically insert rows to the end of the sheet by pressing enter after filling the last row with data, which would then move the totals down. Also, the formatting of the rows I wish to add need to be copies of the ones above.

View 3 Replies View Related

Textbox To Enter Value In Either Format

Nov 27, 2009

On the attached example i have a user form (click Add New Hedge button) and on this form is a textbox for 'Avg. Price'. The value of entered from this box will go into the next available row in column on 'Unsettled Hedges' worksheet.

My problem is sometimes i would to enter the price as a fraction, e.g. 1/2 and other i would like to enter the price as a decimal, e.g. 1.5. At the moment if i enter the price as a decimal figure this will convert it to a fraction in column D of the 'unsettled hedges' worksheet.

What do i need to change in my code to make it enter the price in either format?

View 14 Replies View Related

How To Automatically Update Cell Format In Cells That Reference Another Cell

Feb 22, 2012

Cell C3 has "Joe"

Cell X44 is a VLOOKUP that retrieves "Joe"

Let's say Joe goes on vacation. The workbook user goes in and puts a blue fill in C3. Cell X44 would also need to change automatically.

What's needed to make this happen?

View 1 Replies View Related

Automatically Refresh Without Having To Click Or Press Enter To Get VBA Code To Work

Feb 25, 2013

I have some VBA code which hides columns based on a cell value. The cell value changes according to which option button is selected. The code works but not when the button is selected and the cell value changes. It is necessary to click elsewhere in the sheet or press Enter to get the columns to hide. I want it to do it automatically as a user wouldn't know to click elsewhere.

The working code is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N5").Value = 2 Then
Columns("O:R").EntireColumn.Hidden = True
Else
Columns("O:R").EntireColumn.Hidden = False

End If
End Sub

N5 is the linked cell for the option button.

View 4 Replies View Related

Time Format Why I Enter Colon Between The Hours And Minutes

Oct 28, 2007

I have used the format [hh]:mm in a cell for 24 hr clock calculations. Why do I have to enter the numbers with a colon when I populate the cells? Is there a way to set it up so I just type in the four numbers and the colon between the hours and minutes populates itself?

View 9 Replies View Related

Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month

May 8, 2009

how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:

If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008

Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.

View 3 Replies View Related

Automatically Enter Text In Cell When Due Date In Other Cell Is Exceeded By 272 Days

May 29, 2013

I want to enter the word 'Closed' in cell B1 when the due date in cell A1 is exceeded by 272 days. I have tried using conditional formatting and excel accepts the formula but nothing seems to happen. Here is what I have tried;

B1=IF(A1<=NOW()-272,"Closed")

View 4 Replies View Related

When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell

Jul 10, 2006

I have 6 Headings in excel named...

"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.

There are two projects.

Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.

My Specification follows...

1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.

View 3 Replies View Related

Automatically Copy Formula To Next Cell When I Enter Something In Perticular Cell

Sep 8, 2007

Automatically copy formula to next cell when i enter something in perticular cell ....

View 11 Replies View Related

Automatically Enter Text In Cell Based On Another Cell?

Jul 29, 2013

I am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number.

What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).

Table A
Cab Type
Cab No

Indigo
100

Indigo
758

Innova
1544

Indica
1675

View 1 Replies View Related

Automatically Enter Text In Cell Based On Another Cell

Jul 28, 2013

I am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number. What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).

Table A
Cab Type
Cab No

[Code].....

View 7 Replies View Related

Add Zero Automatically In A Time Format.

Dec 22, 2008

I have been battling with this report I have been working with for sometime. Is there a way in Excel where I can automatically add zero into the time format?

The problem is I have to import data from another source and the time displays as a time format showing :59:00 insted of 0:59:00. Excel will not sum the calculation without the zero infront of the colon. This makes my report very time consuming. Can someone show me a way that I can automatcially add zero's infront when having data under 1:00:00?

View 6 Replies View Related

Automatically Format Header Row

Oct 18, 2006

I sometimes import data from an ERP Program to Excel. I want to write a Macro which will make the imported data more presentable (For example: draw borders for all the cells, give the heading a fill color etc. etc.). To do this I need to select the heading row. The problem is that each time there are a different number of columns.
How do I program the macro so that it will color all of the header? I saw a macro that does this on a pivot table and I want to do thae same on a regular table.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved