I have a range: AC2:AG1400 that are filled with numbers....
Someone manually highlighted, in bright yellow, some of the numbers.
I need a macro that could reverse the signs of only those that are coloured in that yellow.
I have a column of numbers which have the - sign at the back end of the number instead of the front. What would be the easiest way to turn that into a negative number without manually keying each one?
View 4 Replies View RelatedI have a workbook (see attached) that has 2 sheets, Work and Holiday. I want to be able to enter a persons initials into the wrksheet 'Work' for each day of the week.
If that person is listed on the holiday sheet for that day as 'A' then the cell in 'Work' is coloured Red, if not then it is coloured Green. I have tried to get this to work using a defined list and various IF statements but all to no avail.
let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?
View 7 Replies View RelatedVBA macro to change the color of a cell automatically for a specific period of time -say 5 minutes, based on the value the subject cell holds at that time. The cell value is not manually entered but comes from a sub.
There will be hundreds of such cells so that the macro must be able to be repeated for other cells utilizing their individual cell values as well.
This doesn't work
View 13 Replies View RelatedI need to assign the ID value to the cell in the source column when it matches the cell value in the email column.
i.e from the email column the first cell value is bobjohnson@email.com his ID value is 0 I need to find all the cells with bobjohnson@email.com in the Source column and assign them 0.
[Code] ......
I know there is a way to do this, but I can't remember how! I have one cell (actually, thousands of cells) where the first name is listed and then the last name. I want to reverse the text so the last name appears first, and then the first name appears last, in the same cell. That way, I can alphabetize the columns by last name.
column A = Date : 01/02, 07/02, 14/02, 21/02, 28/02 (x-axis : shows when the table is updated)
column B = project: x, x, x, x, ,x (name of the project and trend line)
column C = Delivery Date : 01/05/2009, 08/05/2009, 20/05/2009, 30/05/2009, 28/02/2009 (plotted on the graph)
column D = Status : Green, Amber, Red, Green, Blue (status of the project. the points should be the same colour as is described in the table)
column E = Comments: original, delay, supply, out of money, on track, delivered-wow! (these comments will pop up if the user holds the cursor over a point)
NB Y-axis scale : 01/01/2009 to 31/12/2009 with increments of 14 days. this will be the same scale used for all projects.
Date Project Delivery Date Status Comments
01/02 x 01/05/2009 Green original date
07/02 x 08/05/2009 Amber delay supply
14/02 x 20/05/2009 Red out of money
21/02 x 30/05/2009 Green on track
28/02 x 28/02/2009 Blue delivered-wow
So id like the macro to draw the line for project x based on the 'delivery date'. The points should be coloured according to the 'status' column and when you hover the mouse over the point the data lable will show up taking info from the 'comments' column.
Would it be possible to create a macro that will be able to generate this graph automatically. I have a few projects id like to do the same thing for.
I have a spreadsheet and certain cells are coloured to represent certain
criterier, I would like to display all of one coloured cells to show status
of that colour, say I want all the red cells locations to be displayed so
that i can check the status.
I want to search sheet1 for cells with the colorindex 3 (red), and automatically select the colored cells?
View 4 Replies View RelatedI have a worksheet size roughly 100 * 400 cell. there is roughly 100 cells filled with RED color and with different value.
When i used FIND ALL format - RED, I can locate the cell location and the cell value. And I wish to copy these 2 info onto a new sheet so that on the new sheet, I can create a hyperlink and easily access to the location of the RED cell.
However, in FIND and REPLACE, i have a list of results on what I want. But i cannot copied the information out. So is there anyway to copy it out or to achieve what i want?
For eg.
RED value
cell
50
A3
87
D71
251
AQ5
and so on.
I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:
My boss uses the + symbol and the = symbol in his formulas eg "=+E3*E4" What is the advantage or difference in this as to just using "=E3*E4"
View 3 Replies View RelatedIn a formula, what effect does putting a plus sign after an equals sign? e.g.
=+((1+B8)^12)-1. I orginally assumed that it made sure that result the would always be positive but I was wrong.
This spreadsheet will be tracking sales and purchases. I want to be able to have any purchases formatted as an expenditure (negative sign or brackets) to show it is money out. Column C "Action" has a drop down with either Buy (infrequent) or Sell. I want to be able to have column I "Unit Price" be a negative number if the corresponding cell in the same row in Column C = "Buy". I do not have these prices formatted as currency as their are 2 different currency used and I don't know how to make the correct currency format appear automatically, though I do have a drop down validation column for which currency the transaction was in, so maybe that is possible. I know this is probably simple, but I don't spend that much time using Excel. I have some sample data in their for the moment.
View 2 Replies View RelatedI have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.
When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.
I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.
I would like to be able to put manually a formula in a specific cell (like a "requested date" cell) and then have a table that use this date for the headers and calculate +1 year for each columns.
For exemple:
=2012-01-01
=2013-01-01
=2014-01-01
=2015-01-01
requested date:
2012-01-01
Would you know a way to easily do it? when I try to do it manually (+365) it doesn't consider leap years. I thought about a =DATE formula but the requested date have to be manually entered...
I have an array, let’s say B1:D20. I would like to be able to manually select any cell within this array and then run a macro which will place the contents of the selected cell into the cell in column A in the same row as my selection.
View 3 Replies View RelatedI have the below code that changes the colour of a cell to green if the figure increases, and to red if it decreases.
The issue is that it works fine if you manually change the figure in A1 or A2 by entering the figure and hitting enter. If the cell figure changes in A1 by any other means such as RTD or say a value in C1 "=C1" then the colour change is not triggered.
Code:
Public oldval, myval
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A2")) Is Nothing Then
oldval = myval
If Target.Value > oldval Then
Range("A" & Target.Row).Interior.ColorIndex = 4
[Code] .......
How can I get a cell to change color if a user were to modify or change the value of the cell contents?
View 4 Replies View RelatedI have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:
VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range
[Code]......
I have been using Excel for several years and I'm fairly proficient at its basic functionality. I've recently learned and made extensive use of pivot tables. However, I have not taken advantage of such features as Data Validation, VBA, etc.
View 3 Replies View RelatedI have a spreadsheet where column E needs to be filled with an amount depending on whether another cell in column F has either a + or - sign.
For example:
F2 has "+ ACH PreAuthorized", G2 has "322.1". I would like to have cell E2 fill in the amount "+322.1". So in cell E2, if F2 has a plus sign, then put in the amount in E2. If it has a - sign, put in the amount from G2 into E2 but put a - sign in front of the amount. If there is no sign at all, put a zero or leave blank.
I need it to be like:
=B10 2014
(So the first part changes depending on what B10 is but the 2014 always stays)?
How do I Extract information before a sign "/" in a separate cell ?
View 2 Replies View RelatedI need to enter =+c5,+c6,+c8,+c9 thru +c105 in a cell on5 different work sheets. is there an easier way than manually typing.
View 5 Replies View Relatedimagine your data set (numbers are random and do not start in cell A1):
1
2
3
4
5
6
i need the entire data set reversed
6
5
4
3
2
1
i started trying to code this...... but quickly realized i havn't the slightest idea where to start
Can i take a name that is in "normal" order and change it so that it is in the format of "Lastname, Firstname Middlename" regardless of how many names are represented?
I have used a simple concatenate formula along with some left/right formulas to get this working if every single person in the list has only their first and last name, but I get errors if the person has 3 or more names listed.
So what I want, is a formula that will turn these names:
Carl Johnson
Carl Robert Johnson
Carl Joseph Robert Johnson
into these names:
Johnson, Carl
Johnson, Carl Robert
Johnson, Carl Joseph Robert
I have a cell in my spreadsheet that I use to control the size of a column. I formatted the cell as a number and added data validation to make sure the number is greater than zero. Then I realized that I need to also allow for the option of auto sizing the column as well. This would require a data validation list type with something like True and False for the choices.
What I want to be able to do is have it both ways. I want a drop down list in that cell with the options Auto Size or Custom. If Auto Size is selected the cell value gets set to that. If Custom is selected I then want to be able to type in a number.
ddl.png
[URL]
Is there a way to setup a cell such that the value is either an item from the drop down list or, entered directly (depending on the list selection)?
I know I can do it with dependent lists but that would require two cells, one for the auto size mode (true/false) and the other for manual width value.