How do I set a colour to 4 cells based on the value of a cell
I want to assign a colour to a group of 4 cells (ie a1 to a4) based on the
colour that is typed into another cell (ie d1). It could be that I need the
option of using 10 colours.
eg if d1 was "green" then a1-a4 would be coloured green.
For greater than 3 colors(4 if use default no color) you will need VBA.
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set rng1 = Intersect(Target, Range("D1"))
If rng1 Is Nothing Then Exit Sub
On Error GoTo endit
Application.Enab...Reference to cell with external spreadsheet
I have a lookup function with a very long range name which referrs to another
spreadsheet. I would like to make a cell with the text of the range and just
refer to the cell in the lookup function. Is this possible?
On 19 Dec, 20:43, B Lane <B L...@discussions.microsoft.com> wrote:
> I have a lookup function with a very long range name which referrs to ano=
> spreadsheet. I would like to make a cell with the text of the range and j=
> refer to the cell in the lookup function. Is this possible?
> Thank you
If you long range name is in ...blank cell turns to 0
Using Excel 2000. I am trying to work on a sheet someone else started but
can't finish. In a cell on the sheet that the formula below is pulling the
data from there is a blank field but there is a 0 in the cell that has this
formula in it on my new sheet. If the cell on the first sheet is blank, I
want the cell that equals that cell on my second sheet to be blank too.
='SVMMC-admin, tx, blue cards'!$B$3
The sheet is a yes/no sheet and when there is a 1 in the cell it means yes,
when there is a 0 in the cell it means no, when the cell is blank, it means
Not Appl...Collections Management
Dynamics GP v9 - Collections Management
Users are unable to use "user defined letters" (Word Documents) for mass
mailings. They are limited to only using predefined letters, or printing each
letter individually for every customer.
Error received when users attempt to use a custom letter:
Unhandled script exception:
Cannot find report "COL_Reminder_UpcomingDue".
This post is a suggestion for Microso...Can't find cells to change content?
I have been given an Excel document I'd like to use as template.
However, there is one item of text that I cannot change. In fact I can't
seem to find which cell it is entered in. It appears as an overlay over
the cells and does not relate to them.
I can click on any other item and see to which cell it relates. Inserted
Images highlight, and can be moved and placed anywhere. This text is not
an image either as it does not give the image handles.
If I select the whole document and delete the contents the text goes away.
NS <calypson@bell_kill_this_south.net&...IF STATEMENT REFERENCING A BLANK CELL
I'm trying obtain a blank cell with an IF statement based
on the formula B6-SUM(B9;B11;B13;B15;B17):
1. if B6 contains a reference which returns a blank if the
referenced cell is blank
2. if B6-SUM(B9;B11;B13;B15;B17) returns a 0.
I've resolved item 2 this way:
but everything I've tried for item 1 returns an error
If anyone can help, thanks.
> I'm trying obta...Deleting part of a cell
I have a large number of cells containing text (name of co. or
individual, varying text string length) followed by a space and an 18
character string such as this "Shaving Centers </0016000000IMo40>" - I
wold like to retain only the initial text string (ie. "Shaving
Centers" - without the quotes of course) whether the space remains is
immaterial. Problem is the </*> part is not constant, though I think
it is of uniform length. I tried using the "IF(Right(..." function but
had no success using the substitution for the numbers ie, </*>. If
unclear ...Application-defined or object-defined error
I find myself stumped by an incredibly easy piece of code and one that I
have used before.
I am getting the error:
Run-time error '1004':
Application-defined or object-defined error
I am getting the error when I run the following code:
Private Sub Workbook_Open()
.ScreenUpdating = False
.DisplayAlerts = False
Workbooks("phoneholdtime...I need a macro to find cut and paste data to new cell
I have data that I have exported to excel that I need to reformat to be able
to create a pivot table to check for duplicate entries.
On importing the data which is in the form of journal entries the memo line
is stting above the journal numbers in column D. I need to find all of the
comments and cut and paste them to column C. The comments are not all the
same but do contain the same word "To" in the comment. The journals are not
all the same size and so the comment line does not appear at regular
intervals. Any help appreciated as I am very new to VBA
...Blank Charts and Paste Special
Blank charts seem to be ignoring the settings in the paste-special
Using Excel 2007.
I want to create several x-y scatter plots from different data sets.
There is some non-trivial formatting of the axis and legend and so on
that I want to maintain and not have to do over each time.
So, I made the graph once on one set of data. Then I made a copy. Then
on the copy I deleted all the data series. There are a bunch of
data series on each chart, so I didn't want to copy a chart with the
data on it. Then I made a copy of the now blank chart.
Then I did select-copy on the data for the...Blank password
I have installed office 2000 on a brand new xp pro xp1 machine. The problem
is that password does not stick in outlook internet account. I have to
re-enter the password every time I send/receive email. The relevant registry
entries are as follows;
Windows Registry Editor Version 5.00
"POP3 User Name"="Operations"
"POP3 Skip Account"=dword:00000000
"POP3 Prompt for Pas...Deleting blank rows
How can I quickly delete blank rows from a list I've
imported to an Excel worksheet so that I can after order,
filter the entire list?
Select a column which includes the blank rows. Click Edit/Go To/Special . .
Blanks - and OK. Then Right click on your selection and click Delete . . .
and select Entire Row.
Or, you could sort on a column containing the blank rows and that would put
them all together, for easy deletion.
"Joana" <email@example.com> wrote in message
> How can I quickly...runtime error 2465: application-defined or object-defined error
I'm trying to use a button on a main form to change the sort order of a sub-
subform.When I execute the following, I get runtime error 2465: application-
defined or object-defined error. opting to debug, I find the line with
orderbyon highlighted. Anybody see what's wrong?
Private sub timesort_click()
Forms![control]![worklist]![approved].Form.OrderBy = "Forms![control]!
Forms![control]![worklist]![approved].Form.OrderBy0n = True
Message posted via AccessMonster.com
Im trying to enter two values into the same cell. I've seen it done, it
has a red diagonal line seperating the two values.
Thanks for any help.
MFFC2005's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25816
View this thread: http://www.excelforum.com/showthread.php?threadid=392130
First format the cell as Text. Then enter the two values separated by any
character you desire. The cell will hold exactly what you typed.
"MFFC2005" wro...Blank column to be unique increment
I'm trying to write an update SQL to fill an Integer column with an
My table has 500 rows, I want this new Integer column to be filled with
number of 1 to 500.
Is there a way to do this via an update SQL statement?
Thanks in advance.
Here is one example:
CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
INSERT INTO Foo (keycol, datacol) VALUES (1, 'a');
INSERT INTO Foo (keycol, datacol) VALUES (8, 'b');
INSERT INTO Foo (keycol, datacol) VALUES (11, 'c');
INS...How do I add a new blank record (row)
I have a table that consists of one row for each record. Each row contains
columns for inputing data and columns containing formulas.
I am trying to create a macro that inserts a new row (record) ABOVE the last
row (record) so that the newest rows are at the top of the table.
Would like the new record created by the macro to be blank except for the
cells containing formulas.
I am easily able to create many records below or above the current record.
However, I do not wish to have many blank records above the latest record -
only one blank record at a time when I press a button.
My u...Adding Comments From Cells To Chart Points.
Hi all I have spent a large portion of my weekend trying to find the
answer to this. Without much luck! I have finally come up with my
own code and was hoping someone could have a look at it. I'm looking
to clean it up and make it a little more efficient.
Thanks in advance,
Dim ws As Worksheet
Dim ct As ChartObject
Dim serSeries As SeriesCollection
Dim ser As Series
Dim Counter As Integer
Dim ChartName As String
Dim xVals As String
Dim xAddress As String
'Loop through each worksheet in workbook
For Each ws In Worksheets
'Lo...Defining a variable to = MATCH
Dim thingy As Integer
thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)"
ActiveCell.Formula = _
"=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen
Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))"
my thingy doesnt seem to work..... no comment
Any suggestions on correcting this?
Message posted from http://www.ExcelForum.com
thingy = Application.Match("DiceC",
Sheets("qperiodagentperformance"...Simple "if word exist among theese cells, then true"
Im banging my head on the "find" functions. They give me a -value- when
I need TRUE/FALSE for an IF function.
It must be a much easier way to return TRUE or FALSE if a word exist
within an area:
1: Is "*Apple*" in A1:A3? = TRUE!
2: Is "*Tomato*" in A1:A3? = FALSE!
JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945
View this thread: http://www.excelforum.com/showthread.php?threadid=517716
=N...how to do self-defined regression in excel?
Hi, I want to do regression ananlysis in excel using a
sigmoid function, which is not offered in the general 6
functions in excel(by adding trendline). How can I do it?
The Analysis ToolPak - VBA addin has a regression function
and probably everything else you want.
Tools > AddIns then checkmark Analysis ToolPak - VBA
Go back to Tools dropdown and it should be listed near the
bottotm of the dropdown.
>Hi, I want to do regression ananlysis in excel using a
>sigmoid function, which is not offered in the general 6
>functions in excel(by a...Adding a field to RM Blank Document
I need help adding the distribution reference from the RM Distributon Work
table to the RM Blank Document. Thank you..
...Cell Address in a pivot table
I have a pivot table which gets updated every day.
Last row in the pivot table is 'Grand Total' and the number of rows in
the pivot table varies every day.
I have another function which needs the count of number of rows in the
pivot table. Since the starting cell remains the same, I am counting
the number of rows in the pivot table = Row Number of 'Grand Total' -
Starting Cell value.
Now my question is .. how do I get the Row Number of cell 'Grand
Thank you for your time.
~~ Message posted ...Who do I get Publisher 2003 to open to a blank page?
I'd like to get Publisher 2003 to open to a blank page - no "start" page, no
"new publication" dialog box on the left...just click the icon and a plain,
blank page opens.
silver <firstname.lastname@example.org> was very recently heard to
> I'd like to get Publisher 2003 to open to a blank page - no "start"
> page, no "new publication" dialog box on the left...just click the
> icon and a plain, blank page opens.
Tools > Options > Uncheck "Use New Publication task pane at startup" > User
Assistance &g...User-Defined type not defined?
I recently imported all my data into a blank DB now I am getting this error
Dim wrk As Workspace
Is that something to do with me importing into a new DB
Thanks for any help...........Bob
Sub SelAllNone(Optional SelectAll As Boolean = True)
On Error GoTo stoprun
Dim sqlStr As String
Dim wrk As Workspace
Dim db As Database
Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb
sqlStr = "UPDATE [tblHorseInfo] SET [Worksheet] = " & SelectAll & ";"
db.Execute sqlStr, dbFailOnError
Set wrk = Nothing
Set db = Nothing
...two lines in a cell
I am trying to get multiple lines to display in a cell without merging . I have to display text such a
1. Hello my name i
2. Duncan LeBlan
on two lines in a single cell. The cell width is long and currently I need to insert the appropriate spaces to have the second line of text move down.
The issue arises when I print as the display in either Normal view or Print Preview shows that the text is on two lines, but it prints with parts of the second line on the first line
Is there any command or function that can be used to allow me to get 2 or 3 lineds of text formatted on different lines ...