Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

In Excel-shade the cell automatically when data is entered

I want to be able to enter data into a worksheet, and have the cell that I am
entering automatically change colors or shading.


Post your answer or comment

comments powered by Disqus
Hi,

i attached my sample file to help make things easier. what i hope to do is
1) when data is entered through the userform, it will be sorted according to their expiry dates in ascending order.
2) there is a formula in the "balance" cell which i hope to auto fill to the next row when data is entered.

Thanks in advance

I'm looking for a timestamp to automatically appear in column D when data is entered in columns A, B, and C (so nothing appears if all 3 cells are not filled). In addition, I want those 3 cells to become password protected once data is entered.

This is what I have in column D:
=IF(AND(NOT(ISBLANK(A4)),NOT(ISBLANK(B4)),NOT(ISBLANK(C4))),NOW(),"")

I have managed to get all of this to work out, except that the timestamp all have the same time and updates with the real-time instead of when it was entered.

I have read somewhere that I may need to go to, Tools => Options => Calculation and check the Iteration Box. I have also read somewhere that I may need to go to, Data => Validation => Settings, select List under Allow and select the timestamped cells under Data. Neither have worked!

I've attached the .xls. Please help! Thanks in Advance.

OK, first post for me, be gentle. I am trying to automatically populate a cell with todays date when data is entered (or changed if that's possible) to an adjoining cell, and blank if the adjoining cell has no data. Just a list I want to track when a field is updated, without having to manually populate the date field beside the data, ie

A B C
Date updated
Salesperson 1 Neil 01/02/08
Salesperson 2 (blank)
Phone number 02 === 16/02/08
Fax number 02 === 16/02/08

I found the 'Creating formulas that only return results from non-blank cells which can get me part of the way, but no idea how I can get a 'todays date' return.

Anyone?

Can Excel recognize when data is entered into a cell and then apply the
appropriate formulas from the cells above? I have a spreadsheet set up for
people to use but some of the cells are locked and I don't want to print the
whole worksheet until info is entered into cells and then the formulas be
applied.

You need to tell us what the function returns that woiuld lead you not to
count it as being 'the last data used'.

Various ways to do what you ask, but we have to know what criteria you use
to ignore the results of a function, ie perhaps it returns a blank, or a 0
etc.

Assuming your function returns a blank if 'data not used' then something like

=LOOKUP(99^99,C4:C8)

might do you.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)

"Alex s" wrote:

> A created a schedule where I input data in cells B4-B28 and B4-Y4. I used a
> function to copy the data from the previous cell from the previous column
> (ex. C5 is copied from B4, D5 copied from C4, E5 from D4 and so on...)
> I want D9 to check the previous rows in the previous column and used the
> last data that was used. I want D9 to check to see if C8 has data and if it
> doesn't then check C7, then C6, then C5, and C4. Lets just say the C4-C6 has
> data, I want D9 to automatically use the data from C6, which is the last cell
> with data. Keep in mind that C4-C8 already are using a function. If anybody
> has an answer i would really appreciate it. And if you have a question, I'll
> be more than gladly to try to explain it better. Thanks.

Hi Everyone. I need to run a macro ("ReformatData") everytime that someone adds or changes data in a table. So far I have the code below in the VBA project box (Excel 2010) which only works when I "manually" select a cell and change the data, but not when I right click to insert new data or operate "control V" to paste new data over old (which is 99% of the time)

Could any one suggest a code which will run the macro when data is pasted, inserted or "manually" changed but NOT when a superficial alteration is made such as colour formatting or using the automatic filters? Thanks in advance for giving this some thought.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("$A$31:$L$10000")) Is Nothing Then

Call ReformatData
Range("A1").Select
MsgBox "Reformatť"
End If
End Sub

Hi everyone,

Sorry in advance for my very lengthy explanation and question! This is my first post and I hope this isn't a duplicate - I searched these boards and the most recent similar question to mine was in 2004. After 2 1/2 days of searching the internet and reading various forums, I can't seem to come up with the right macro or vba code to have a worksheet automatically protected after a user enters data in a specific column. Let me say up front, I have pretty much no experience with macros or vba code, but my basic knowledge of Excel is good. I've created a spreadsheet that I basically want to use as a time clock. I found vba code that places a static date/time stamp in Column C when a name is entered in Column B. This works great. What I am stuck on is the protecting part. I would like the entire worksheet to be protected automatically after data is entered in Column B and for this to be transparent to the user. I've already got the cells in Column C locked, but that does no good until the entire worksheet is protected, and I don't want the users to do the protecting. Without the worksheet being protected, they can delete the time stamp. I also need the worksheet to automatically unprotect upon opening. Any suggestions would be SOOO appreciated! Here is the code I already have in place for the time stamp:

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToCheck As Range
Dim myIntersect As Range
Dim myCell As Range
 
Set myRngToCheck = Me.Range("C:C")
 
With Target
Set myIntersect = Intersect(myRngToCheck, .Cells)
If myIntersect Is Nothing Then
Exit Sub 'nothing to do
End If
 
Application.EnableEvents = False
On Error Resume Next
For Each myCell In myIntersect.Cells
With myCell.Offset(0, 1)
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.Value = Now
End With
Next myCell
On Error GoTo 0
Application.EnableEvents = True
End With
 
End Sub
I hope I've supplied all of the relevant details - if not please let me know.

Thank you in advance!

Hi there,

I need to create a spreadsheet where I want to force the user to enter data in the right order.

So, for example, I want cell B5 to be locked UNTIL data is entered in A3, A4 and A5, BUT for the remainder of locked cells - column C say - to stay locked.

I recently found this answer to a similar problem:

"The first thing you need to do is to protect the worksheet. By default all cells are "locked". Select the cell(s) you wish your user to enter data into. Right click and choose Format Cells. In the Format Cells window select the Protection tab. De-select the Locked checkbox and click OK.

I usually fill the user data entry cell with the pale yellow color to draw the users eye to that cell(s). With the desired cell unlocked, go to the Tools menu and select Protection - Protect Sheet... You can accept the default and click OK. For this example I did not select any other attributes nor added a password.

Open the VBA Editor (Alt+F11) and on the right in the Project-VBAProject pane locate your Workbook in the tree. Open the Microsoft Excel Objects and select your Worksheet.

Paste this code in the code pane:

Private Sub Worksheet_Change(ByVal Target As Range) 
If Not Intersect(Target, Range("B5")) Is Nothing Then 
ActiveSheet.Unprotect 
End If 
End Sub
Note: change
to reflect your user cell. 

Still in the VBA Editor in the Microsoft Excel Objects for your Workbook locate ThisWorkbook. Paste this code in the code pane:

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
ActiveSheet.Protect 
End Sub
When the WB is closed it is set back to Protected.

Save the Workbook"

However, this turns off protection for the entire sheet once data has been entered.

Does anyone know how to change this so that only a certain range of cells are unlocked, not eveything?

Thanks very much for any help!

When data is inputted into a cell I would like to capture the date of when
that occurred. How could I do that and then utilze that date in a formula
later on?

Any help would be appreciated...

I have a two-part question, to which I believe there are easy answers.

1) I've designed a simple commission schedule (for sales) on a spreadsheet, and all the formulas work fine. My question is, how do I automatically fill the formula in the cell above, to the cell below, when data is entered on subsequent columns?

For example, assuming column 1 contains field headers, column two contains cells that require manual data entry (example order number, client name, product type, etc.) and formulas (example calculating commission and percentage amount), when I start entering data on column three, cells that require formula calculations, are "automatically filled" from the cells in column 1. Likewise column four, five, six, and so on.

Let's say A1 is ORDER NO., A2 CLIENT NAME, A3 PRODUCT TYPE, A4 COMMISSION, and A5 PERCENTAGE headings.

B1, B2 and B3 will require manual entries. B4 will calculate commission; likewise B5 will calculate percentage, based on formulas created.

Once I start typing in C1, I would like the formulas in B4 and B5 to be automatically filled into C4 and C5, without using the fill handle, or manually copying and pasting said formula(s). So and so forth with D1, E1, F1, etc.

Hope I made this clear and haven't over complicated my explanation.

2) I often receive spreadsheets containing 9 digit zip codes in a text format. After opening the text file in Excel, I can easily reformat it to read 00000-0000 either by using FORMAT CELLS tool ("ZIPCODE + FOUR" or CUSTOM). I cannot use these tools, if the first number in the zip code is "0" (zero).

Example, how do I reformat 1234-5678 to read 01234-5678? Incidentally, is there a way to preserve the zero, and not have it fall off, if I resave the spreadsheet in .txt format?

Thank you ever so much!

Okay, my tool goes a little something like this:

In Excel, it creates an entryform where data is entered into various combobox and text fields.

The tool then opens Word, loads a pre-made template file with pre-defined bookmarks, then inserts the data from the entryform into those bookmarks.

My previous version used the insertion bookmarks and worked fine for when I was simply entering the data from the entryform at those points.

But for the next version, I need to insert the data INTO those bookmarks (ie a fillable form) so that I can then recall the data from the Word file back into the Excel tool.

Make sense so far?

Using my old code doesn't work. I receive an error message: Run-time error '438': Object doesn't support this property or method.

I've tried all sorts of combinations of code to get this to work and still not getting it right. Clearly, I'm not understanding the error message.

My code looks a little something like this:
--------------------------------------------------------------------------------
'
' Open the Word Program and load the TcubedCVR-newertoolform.dot template.
'
Set wrd = CreateObject("Word.application")
wrd.Visible = True
Set CVR = wrd.documents.Add("TcubedCVR-newertoolform.dot")
CVR.Protect Type:=2, Password:="12345"
'
' Store form fields into Word bookmarks
'
CVR.Bookmarks("TcubedTchr").Text = TcubedTchr
CVR.Bookmarks("School").Range = School
CVR.Bookmarks("VisitDate").Range.Text = Date
CVR.Bookmarks("VisitTime").Range.Text = Time
--------------------------------------------------------------------------------

You might ask yourself why am I protecting the document. Well, if I don't the text inserted replaces the bookmark. But when I protect it so that the file is a fillable form, I can manually insert text/data into the bookmarks without losing them. So I'm trying to automate that process by having the tool insert into the bookmarks the data from the fields.

Any help is appreciated! Muchos Danke!!

Hello Everyone,
I have a formula in cell A4 that multiplies cell A1,A2 & A3 together but if data is entered into the cell I lose the formula. Is there any way to get around this.

Thanks
kdilas

Hi,

On once worksheet i have 4 graphs one above the other. On another
worksheets there is a table to enter data relevent to each graph.

What im looking to do is that when the data is entered on the other
workesheet it either unhides a cell under the respective graph showing
the comments or it adds a new row with the data present.

Is this possible?

Best Regards

Ian Grey

--
greyo
------------------------------------------------------------------------
greyo's Profile: http://www.excelforum.com/member.php...o&userid=26053
View this thread: http://www.excelforum.com/showthread...hreadid=393974

Hi,

On once worksheet i have 4 graphs one above the other. On another worksheets there is a table to enter data relevent to each graph.

What im looking to do is that when the data is entered on the other workesheet it either unhides a cell under the respective graph showing the comments or it adds a new row with the data present.

Is this possible?

Best Regards

Ian Grey

I have been looking for a macro that will automatically insert the date in column a, time in column b, and userid in column c, when date is entered into column d. Can someone help me write this? I would greatly appreciate it. I believe it is a Workbook_Change Event but I am having trouble writing the code to reflect the variables involved. Any input would be greatly appreciated. Thanks!
SHEL

Hi

I need to be able to shade a cell grey if data is entered into another cell

ie

If data is entered into A1 and or B1 then C1 gets shaded and protected

If data is entered into C1 then A1 and B1 gets shaded and protected

If the data in either cell is deleted then the process is reversed

Anyone able to help?

TIA

Dan

As in my previous question I require that zero values only show when data is
entered in adjoining fields. What I failed to mention is that the field that
I want the zero values to show up in is a calculation (=SUM(C2-F2)+D2). How
do I combine your suggestion with my calculation?

Please let me know if you come up with a solution for my print problem as
well.

Thanks
Happy New Year

i am trying to create a timesheet that calculates cells automatically when
data is entered. I am entering time "in" and "out" two times (first half of
day total, then second half of day total, after meal break) and then i have a
formula which calculates those totals. In algebraic terms, say: a+b=c, then
for the second half of the day: d+e=f. then c+f=h, to give total hours
worked. what i need to have is a formula that calculates a new cell (say,
"i") so that IF h is greater than or equal to a value of 8.0 (hours per day),
then it automatically calculates the amount of hours over 8.0, keeping the
regular hours worked 8.0 in cell "h", and calculating the overtime hours as
anything over 8.0 into new cell, "i". hope that makes sense? can anyone
help me? thank you!!!

i am trying to create a timesheet that calculates cells automatically when
data is entered. I am entering time "in" and "out" two times (first half of
day total, then second half of day total, after meal break) and then i have a
formula which calculates those totals. In algebraic terms, say: a+b=c, then
for the second half of the day: d+e=f. then c+f=h, to give total hours
worked. what i need to have is a formula that calculates a new cell (say,
"i") so that IF h is greater than or equal to a value of 8.0 (hours per day),
then it automatically calculates the amount of hours over 8.0, keeping the
regular hours worked 8.0 in cell "h", and calculating the overtime hours as
anything over 8.0 into new cell, "i". hope that makes sense? can anyone
help me? thank you!!!

I want to automatically change the colour of a cell in Excel when data is
added to that cell. For example: an empty cell has a blue backcolor when a
number is put into the cell the background automatically changes to white.

Hi all,

I have to run a macro which is based on the formula A = A - B. I have a 8x3 grid where the columns are Total, Current, Remaining. 'Total' will have details entered by the user initially. 'Remaining' will initially take up values of the 'Total' column. Now I want that when data is entered in 'Current', 'Remaining' should automatically calculate:

'Remaining' = 'Remaining' - 'Current'

I tried writing a code in the Worksheet Selection Change window but that just runs the code every time cell selection changes which gives inaccurate results. Is there a way that the formula is calculated only when the data is entered in the cell and the user moves into the other cell? I hope I have been able to express the problem clearly.

I have set up a sheet with training dates, now I want the cell to change the collor automatic when the date is longer that two years in the past and then the row must calculate to a %, only the valid dates in the specific row based on the collor or the date.

Hey all, Need some direction, I have two worksheets one is a summary the other is Expenses sheet
What Iím trying to do is when data is entered into my expenses sheet (cost) cell D3:D35 that itís automatically added to my summary sheet matching the dates M5:M35
Thanks

******** ******************** ************************************************************************>Microsoft Excel - BOWLING 2008-2009 Updated.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE4E5I5A6E6I6A7E7I7A8E8I8A9E9I9=
ABCDEFGHIJKLM3DateWeekLEAGUE FEES DUELEAGUE FEES COLLECTEDBalance50/50 SOLDSTRIKE POT SOLDBOTTLE SOLDTOTAL COLLECTEDLEAGUE FEES OUT50/50 OUTSTRIKE POT OUT OTHER EXPENSES4OPENING BALANCE   $15.00        528-Sep-0811,540.001,445.00-95.00133.00123.001,701.00393.3666.0086.00 65-Oct-082975.001,085.00110.00152.00138.0078.001,453.00420.1876.005.00712-Oct-083   
    
    819-Oct-084   
    
    926-Oct-085   
    
    2008-2009 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

******** ******************** ************************************************************************>Microsoft Excel - BOWLING 2008-2009 Updated.xls___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA4A5A6A7A8=
ABCD2Date DescriptionAmount328-Sep-08 Sanction fees last year to$43.00 45-Oct-08 Sam's Club Tickets for Draws$31.47 512-Oct-08 LCBO 3 bottles for draws$105.25 619-Oct-08   726-Oct-08   82-Nov-08   EXPENSES 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I'm working with a spreadsheet (MS Office/Excel 2003) that someone else
set up, and certain cells, which I add to every day, will automatically
fill in with a background color when data is entered, which is pretty
nifty. But certail cells don't, even though they should (should meaning
that color-scheme wise it would be nice if they did). But I can't
figure out why they won't or how it's programmed to know when/and what
color to do that with. The strange thing is, I know these cells
(they're in a particular column) used to do that, and some of the other
cells *sometimes* don't auto fill the color, even though the same type
of data is put in everyday, it's sort of random. I've looked through
all the Cell and Column properties and can't figure out what settings
make this happen.
Any ideas?


No luck finding an answer? You could always try Google.