I am trying to get different people to add dates onto a spreadsheet but need a way to differentiate whos dates are whos. Is there a way to color the cells according to which user put the dates in automatically?
View 1 Replies
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies View Relatedfind attached zip file . if you open the file then you can understand the problem.
I need to change cells color and text color as per user choice in Password protected sheet : (user is useing excl2000)
Kindly open attahced file i putted the note inside the file.
I have a cell which team members enter their team name in to. For example FR1 and RA5. I set up data validation to only allow four characters so they can enter RA10 FR15 etc. However I have now found some are entering FR 1 with a space between the team name and number. This then means my formulas further on are not picking up the cell correctly. How can I have the space between the FR and 1 removed automatically upon user input?
View 4 Replies View RelatedIf the user inserts a row, I want all formatting/formulas/data validation from the row above to be inherited in the newly inserted row. I want this to occur on sheet1.
When you insert a new row, although the entire new row is selected, the active cell is the cell in column A. So I figured I could base a routine off of the active cell. Here is what I tried:
[Code] .....
I am trying to reference a range. The first cell in the range is above the active cell. The second cell in the Range is above, the active cell, then to the right of the range, then down one. By referring to this Range, I am then trying to use the FillDown method. This code isn't doing anything.
Also, I need Excel to automatically run this code when the user inserts a new cell on sheet1. Would I need an event handler or just put this code inside of the Sheet1 code window?
Using Excel 2010.
The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)
In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.
The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8
I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".
VB:
Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8
On Error Goto handler [code]....
There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across
Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the
same errorExecuting the subroutine on its own (with the absolute range) works OK
So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
1. I want to highlight cells on a form so that a user knows which cells to fill out.
2. When they print the form I do not want the cell shading to print.
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies View RelatedWorking in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
So i've been trying to do this invoice/inventory/client , I couldn't find a way to somehow register the whole invoice as it is, so i can later print/visualize it if i lost the copy or something.
It would be best if it just stayed stored in a variable and not always visible, and only appeared if i wanted to check/print it.
The whole invoice would be from A1 to F30
Excel 2010 btw
What I am looking to do is;
If cell A2 = "Closed"
then I want cells B2 and C2 to strike through its own text.
I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah
but if
A2 = Closed
then B2 and C2 = Blah blah blah
I want to administrate a short training test. The user has chooses a correct answer A-E, off a drop down menu. I want to set it up so the user can't answer multiple times until they choose the correct answer.
View 14 Replies View RelatedSay you have two columns, A & B. Each cell in column A consists of an identical drop-down menu of four possible choices. Each cell in B is a random number. I want to sum the cells in B according to what the user selects from the drop down in column A. If A1=A2="BILL", I want the sheet to add B1 and B2. IF A1=A3="BILL", and A2=A4=TOM, I want the sheet to sum B1 and B3, then sum A2 and A4. So, conceptually, I guess column A acts as a group identifier, and I want to sum B by group.
View 2 Replies View RelatedI'm trying to force the user to fill in three of the four available cells.
Initially they have a choice of filling in data for either:
Sheets(1).Range("C4")
or
Sheets(1).Range("C6")
But after that both Sheets(1).Range("C8") & Sheets(1).Range("C11") cells must also be filled in.
So it's either cells:-
C4 + C8 + C11
Or
C6 + C8 + C11
I'm trying to create a macro for another user that she will use repeatedly for many different spreadsheets. Each spreadsheet will have a varying amount of rows that will need to be exported to a different file. She will highlight the cells she wants exported and then hit a button to do so. My problem is this: how do I code a macro so that it will know which cell is the beginning of her selection and which is the end?
View 2 Replies View Relatedi have just read the thread started by scabertrain regarding "if" conditonal formating, but mines slightly different and i cant make head nor tale of the fomula shown.
So... Monthly budgeting... using a projected outgoing (A1) and an actual outgoing (A2) i want the colour "A2" to change depending on whether i have spent over the budgeted amount, green being underspent, orange for on target and red for over spending....
Example:
I predicted (A1) that i was going to spend £50 on a night out, but my actual spending (A2) was £100, i would like A2 to turn to red.
I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.
conditional formatting.xlsx
Creating a project tracking sheet that is as automted as possible so that people dont have to populate lots. I have managed to get it to create a new sheet and populate according to a filled out combobox. The user then populates a 'milestone' section. When a button is clicked these are transferred to a GANTT chart type sheet and pasted. Before they are pasted the macro checks if the project title exists, if it does not it first pastes the title LEFT INDENTED. Then follows by pasting the milestones CENTRED. If the title exists, it inserts the milestones under the project title by copying and pasting all milestones. Hence, if milestones are regularly added, it will begin to add duplicates of the same milestone. I need to remove these. I would usually be able to do this however there is often the same milestone in numerous projects which I CANNOT remove as it would loose data. I would like a code that says between the cell I was searching for (Rng) and the next cell which is also left indented to remove duplicates. Basically I can't use x1enddown because it needs to stop at the next cell which has the same formatting... i.e. only duplicates to be removed out of the cells which are centred. I would then like it to repear this action for the whole document and move the next left indented cell and do the same.
It feels like a kind of backward conditional formatting is what I am looking for
Here is my code as it stands
Dim FindString As String
Dim Rng As Range
FindString = Range("D2").Value
If Trim(FindString) <> "" Then
[Code]....
I have a very simple userform that has name, address, phone, etc.
I want the command button the insert the info from the user form into certain cells in my spreadsheet then clear the info out of the userform and close it.
Have a worksheet that will be used multiple times. User will enter name and employee number only on the first instance that the worksheet is opened. Would like to protect that info automatically for further usage. Most of the worksheet is protected but some cells need to remain unlocked so the "whole" sheet protection will not work.
How do I protect only specified cells, say F3, G3, H3, I3, after the user enters data thru an input box without changing any other protected cells on the sheet?
I have a range of cells that I want to sum based on a range inputed by the user. The range begins with cell c27 and ends with cell au27. I want the user to be asked the beginning cell and ending cell of the range they want to sum and then output the answer.
View 4 Replies View RelatedWhat I am after is a user form that pops up when I enter ‘Y’ in cells in Column ‘L’ which displays the contents of the cells in that row e.g. if I enter Y in cell L2 a user form would pop up displaying the contents of cells A2 to M2
The A2 to M2 column headers are:-
First Name
Surname
Code
Address line 1
Address line 2
Address line 3
Address line 4
Address line 5
Post code
Telephone
Comments
The user form can be Read only i.e. I do not need to edit the cells (although that would be good) as it will be used as a source document to input into another application, however, a ‘Print’ button to print the form would be beneficial. The only other button required would be a ‘Close Form’ option.
I want to do a concatenate for multiple fields, but want the user to specify which cells should be concatenated by entering this in a few fields. What does this formula (or VBA) look like?
Let's assume there is the possibility to concatenate a maximum of 4 cells. Call the 3 cells that will be concatenated cell1, cell2, cell3 and cell4.
On the first sheet of the workbook the user enters the following:
cell1 = Sheet2!A4
cell2 = Sheet2!B4
cell3 = Sheet2!C4
cell4 = Sheet2!D4
Here are some cells and their values:
Sheet2!A4 = Hel
Sheet2!B4 = lo
Sheet2!C4 = World
Sheet2!D4 =
Upon enterring the information after the = sign for the cell1, cell2 and cell3, the formula for the concatenate becomes: =CONCATENATE(Sheet2!A4, Sheet2!B4,Sheet2!C4 ).
The result of the formula is: HelloWorld
If I now change the values of cell1, cell2, cell3, and cell4 the following should happen:
cell1 = Sheet2!B4
cell2 = Sheet2!C4
cell3 =
cell4 =
The formula for the concatenate becomes: =CONCATENATE(Sheet2!B4, Sheet2!C4).
The result of the formula is: loWorld
I've been trying to find something that can do the following. I would like Column D and E to auto fill with a repeated data when the user fills out column B. For example,
Column B | Column D (auto filled) | Column E (auto filled)
101 | 1 | 1
102 | 1 | 1
103 | 1 | 1
I am trying to display number of lines which depends on value pass by user. Actually user is passing some value. on which some group of lines has to be display.
eg
for 1 value 9 lines
for 2 value first 9 lines + another 9 lines
for 3 value first 18lines + 9 lines
and so on till 52.(this 9 lines are set of some column and rows)
thus cant use macros..
My worksheet with macros gets messed up when a user selects either multiple cells from column F or selects multiple cells in any one row (but only if column F is included) in the range C17:O116 and hits "delete". I will either do an exit sub, an application.redo, or something similar so that this doesn't happen, but I don't know how to write the code to trigger these events. So I need a way to determine "if the user selects multiple cells in column F and hits delete" or "if a user selects multiple cells in any row from 17 to 116 that includes the cell in row F, and hits "delete.""
Set Rng = Range("F17:F116")
Set rng2 = Range("K17:K116")
Set Rng3 = Range("L17:L116")
I need to import some spreadsheets that are sent to me into a master database spreadsheet. I have attached both below. The complaintstest.xls is the master database and the other is the sheet that will be imported. What I would like to happen is when the macro is run, the user will be able to select the sheet they would like to import through a dialog box. After the sheet is selected the macro will import the cells from the selected sheet into the master database in a particular order. In the master database example on row 2 I have listed the columns from the sheet to be imported in the corresponding column on the database sheet. This will macro will be run a couple of times a week so the macro will have to find the next empty row to start the import on.
View 14 Replies View RelatedHere is an excerpt of my code
Code:
Else
Sheet8.Unprotect Password:="rm123abc"
Sheet8.Range("V" & rw) = "Item is not found in the Data Base. Please Contact DME for all documentation requirements for this item"
Sheet8.Protect Password:="rm123abc", AllowFiltering:=True, AllowInsertingRows:=True, AllowDeletingRows:=True
End If
Unfortunately, once the code has run and has finished re-protecting my sheet, it doesn't allow the user to select locked cells. These locked cells contain hyperlinks, and they need to click on these hyperlinks to navigate through my workbook.
So I have two questions:
1. How can I enable allow user to select locked cells
2. Do I have to allow the user to select locked cells to access my hyperlinks, or is there another option?
I currently have this macro that helps me SUM all the cells that are bold.
Public Function SumBold(Rng As Range)
Dim Total As Double
Dim Cell As Object
Application.Volatile
For Each Cell In Rng
If Cell.Font.Bold Then Total = Total + Cell.Value
Next
SumBold = Total
End Function
I want to have the same kind of Macro, but that could COUNT the number of cells that are Bold.
