Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Add Red X In Cell Based On IF Function

I have a numerical value of in cell B3. A user will type a value into cell C3. If the user enters a value other than the value in B3, I would like a space and a red X to appear just after the value they enter, in red. (The value is black; the X is red.)

Example: 100 X

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Chart With VBA Based On A Row With Values That Will Color The Offset Cell Interior Red And Also Give It A Value Of 1
I'm trying to make a simple chart with VBA based on a row with values that will color the offset cell interior red and also give it a value of 1. (look a the example sheet.)

View Replies!   View Related
Conditional Formatting: FIND Function? (get Cell A2 To Fill Red When Cell A2="Public" And A1 Contains "(P)")
Cell A1: International Studies Academy (P) 9-12
Cell A2: Public

Is there a way that I can get Cell A2 to fill red when cell A2="Public" and A1 contains "(P)"?. I can't seem to get a formula to work yet.

View Replies!   View Related
How Do I Write A Positive/negative And Red/green Function?
I'm trying to make a "weight-based" spreadsheet. I want to show a difference between numbers so if an item lost 5 lbs, there would be a "-5" and it would be in green font showing good.

For example, a box weighs 20 lbs and then drops down to 15 lbs. This would be a good result. So I would have 20 lbs in A4 and 15 lbs in A6 and I want to show the difference in A8. However, since I want the box to lose weight, I want it to show a negative value of "-5" in green font meaning it is good. And vice versa, I want it to show "+3" in red font if the box gained weight.

I can't really figure out how to get the signs working properly. I'm assuming to use an "if" function in where if the difference is a loss, show the negative sign and have it in green font. And if the difference is a gain, show the positive sign in red font.

View Replies!   View Related
Checkbox Function To Add +1 To Cell In Different Sheet
I need to include in my roster sheet that if a client is "present" my instructor will check the box. This (idealistically) should add +1 on the column labeled "classes attended" in sheet labeled 5 groups. I've been at this for 3 hours and still have gotten NOWHERE

View Replies!   View Related
Add Worksheet Function To Cell Via Macro
I'm having some problems with trying to reference cells on another sheet with VBA.
I've tried using both of the following but i am getting an error. I get object doesnt support this object or method when I do "'Master List'!" or type mismatch when I use Sheet1.Range($$:$$) I'm new to vba, sorry if this is a dumb question

With Target
Cells(Target.Row, 33).Formaula = "= SUMIF('Master List'!B3:B190" & ",A" & .Row & "," & Sheet1.Range("AR3:AR190") & ")"
End With

View Replies!   View Related
Add Cell Comment Through Macro Or Function
I need a quick function or a Macro which can add cell comment based on selected cell.
let me explain in detail

a1 Mr A wrote
a2 Mr B Checked
a3 Mr C authorised

etc etc.
now i want the same in Cell B as its cell comments
so after b1 comment will be Mr. A wrote & so on

View Replies!   View Related
Workbook>Beforesave Function To Add Cell Borders
I have a spreadsheet that I'm working on and I'd like to include a beforesave macro to format the spreadsheet. Basically I need a macro to select all rows with data in column 'a' and add a thick border to the top of the cells. Ideally the border would stop at column 'i' but that's not essential. I've attached an example of what I'd like. I've included 2 sheets 1 called 'before' and 1 called 'after'. They show what I'm after.

View Replies!   View Related
DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
I am trying to return a TRUE or FALSE based on a date in a cell.

for example:

if cell A1 = 07/11/2009

I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.

I have tried stuff like:


but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?

View Replies!   View Related
Add Nest Formula/Function To Cell With Variables Via Macro Code
I would like to use a varible Cell for the following forumula:

ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"

I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).

View Replies!   View Related
Nesting "if" Function: Fragment Marked In Red Is Currently Missing
In L45 I need function


Fragment marked in red is currently missing and if I want to use it, I would be nesting too many if functions again. To describe the situation: Row 41 is staff available for task, sometimes there is nobody doing the task therefore function will force value 0 in all variances if there is no staff available.

There are also occasions that despite having a number of staff allocated to do the task, nothing’s been received due to a various reasons, this is where I have designed a function calculating hourly variance against what was received against the planned target for this hour. When there is no staff – everything will be 0
When nothing’s been received – target for the hour will be negative. When there was something received – the difference between last receiving figure and current one, minus target for an hour will provide the hourly rate, but If nothing’s been received for the last couple hours, it will go to the last hour when something was received and calculate hourly rate basing on the difference between current figure, last available figure and target for an hour. And here we are at the heart of the problem – in the last cell I cannot use same function as I would have to nest too many “if” functions, therefore it does not include receiving figure in E44 at the moment.

View Replies!   View Related
Macro Code To Add Sum Formula/Function, With Variable Rows, To Cell
I am having to copy and paste rows of data into a new worksheet where the rows sizes change and I am wanting to add a new row at the end of the pasted rows but with the sumation formula to add the relevant column

e.g copy range B14:AA17 with in this case columns E to AA holding the numerical values. Therefore I wish in cell E18 to sum the value of E14:E17 and so on ending with cell AA18 holding the sum of AA14:AA17

As these vary I have all relevant variables, Range to add sumation values to eg E18:AA18
Start Cell E14 and so on.

I tried adding "=SUM(x:d)" where x and d are vars relating the the column cell required eg x = E14 and d = E17

View Replies!   View Related
Add Value Based On Cell Content
I have a named range (D:16 F:800) called “NameRange” which contains names.
I’d like to create a macro which offsets a letter “x”, 10 columns to the right everywhere that name appears within the range. As an example: If I’m trying to find the name Tom, A dropdown menu shows the name “Tom” ( which I’ve created) whereby it calls upon a script to search within the named range from this:

Bob Kim Tom
Tom Sue
Mary Bill Ted
Tom Sue Bob
Fred Bill Sue
Bob Tom Kim

and offset “x” to look like this:.......

View Replies!   View Related
Add Row Based On Cell Contents
I want to add a row underneath a row if the value in the in B cell is not = "". Then i want to write a value in the C cell of the new row.

View Replies!   View Related
Add Cell Value Or Clear Based On Another Cell
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("AQ19")) Is Nothing Then
Application.EnableEvents = False
If Target.Value = "" Then
Range("AW38").Value = "X"
Range("AW38").Value = ""
End If
End If
Application.EnableEvents = True
End Sub

Cell AQ19 will either be blank or an have an O in it.
If it has an O in it then then AW38 will be blank but allow for a manual user input. If AQ19 is blank then AW38 will simply have an X in it. It simply is not working as planned. I have posted this question in this forum as well:

View Replies!   View Related
Cell In Any Row Has A Color Of Red, Put A Yes In The Cell
I have a worksheet that has grey cells and white cells. Whenever I have an error in any cell in any row or column throughout the worksheet, the cell is colored red. This would be for things like; missing data, incomplete data, wrong data etc.

One of my columns is named "Problem" which is NEVER in a fixed position, so the macro MUST look for this column by name, rather than by column position. This column has no data in it whatsoever. This column is there in anticipation of this macro.

What I would like is for Excel to put the word "error" in the "Problem" column in the same row that a red cell/cells are found. Regardless of which column or columns the red cell/cells are in.

View Replies!   View Related
Add Color To Cell Based On Selection Made
I've tried to do conditional formatting for each of the "Discipline" values in this spreadsheet but it is restricted to only 3. Is there a way around this? I'm trying to setup a color code for the "Discipline" values for example RN=Red, LPN=Blue, Social Worker=Orange, Chaplain=Purple, STNA=Yellow, Volunteer=Pink & Other=Green. So when the "Discipline" cell/cells on each of the patient sheets (ie. "Doe, Jane", "Doe, John", etc.) is equal to say RN the cell would then turn Red either on the spreadsheet or only when sheet is printed.

View Replies!   View Related
Add X Days To Date Based On Time In Another Cell
I have a date in col A and a time in col B. I'm trying to use an if function so that if time in Col B is greater than 15:30, the date in col A is returned plus one day. If time in col B is less than 15:30, date in col A returned with no change.

View Replies!   View Related
Add Hyperlinks To Range & Link Based On Cell Value
I have a spreadsheet of various works-in-progress items, the first three columns of which refer to relevant documents, effectively the request, the estimate and the work order, i.e. RXXX-0001, EXXX-0001 & WXXX-0001.

So the text is entered as above (without file extension as it's also our reference number), and I'd like each to be linked to their respecive .doc files, which are held in different locations, i.e. \servernamefolderRequests or Estimates or Work Orders
e.g. \servernamefolderRequestsRXXX-0001.doc

There's a backlog of over 300 items (so 900 links) so I don't want to go through and manually create the links and as each day can see over 12 items being created it'd be much easier to have it done at the click of a button

Most of the solutions I've seen involve creating a new cell/button with the hyperlink in it - I just want to effectively "right click, create hyperlink" but with the cell contents inserted as the file name (adding ".doc") and with correct folder name too.

FYI the cells are adjacent to one another e.g. A1, A2 & A3 so the selection can be Offset (0,1) for the estimates and work orders.

I don't mind if it ends up being a case of running the macro on each line - some links have already been created already.

View Replies!   View Related
Prevent Macro From Running If Cell Is Red
I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.

View Replies!   View Related
Add New Data To Existing Cell Based On Multiple Selective Inputs?
I have a spreadsheet of courses required to reach a certification. On this spreadsheet I have listed the number of hours required for each course in one column, and how many hours I have accrued in an adjoining column. Not all the hours will occur at once, so I tend to bound from cell to cell adding hours in small amounts. What I am trying to do is create a macro that will allow me to add to the existing number of hours to the newly accrued hours, without typing over what is already there.

For example…Class 1 requires five hours total, and I have two hours accrued. If I accrue two more hours (for a total of four hours) I want to update cell E2 without going in to this cell manually and changing this number. I would like to enter the additional two hours in a text box or similar function, and have that function update E2. To add to the level of difficulty, there are four levels of class. This means not only do I need to be able to select which class hours need updated, but which level of class. I have attached the spreadsheet I am working with to try to make things a little clearer.

View Replies!   View Related
Cell Bold And Font Size 12 And Highlight In Red
I am using this formula (Below) and it is working great. Only problem is that I want the client name to be be bold and font size 12, then I want it to highlight in Red. I also want the rows in colunm L to highlight in bright green.

Sub InputData()
Dim varUserInput As Variant
Dim LastRow As Integer

LastRow = Cells(Rows.Count, "G").End(xlUp).Row + 1

varUserInput = InputBox("Enter client name:", _
"Client Name", "")
If varUserInput = "" Then Exit Sub
Range("G" & LastRow).Value = varUserInput
Range("G" & LastRow + 20).Value = "Total".......................

View Replies!   View Related
Add To A Cell Based On Value Of Another Cell?
Cell C1 can have any one of the following values: Light, Medium, Heavy, Super Heavy. In A1, I want to add 5 if C1 is light, add 7 if C1 is Medium, add 10 if C1 is Heavy, and add 13 if C1 is Super Heavy.

View Replies!   View Related
Copy And Paste A Row When A Cell Is Filled With The Color Red
I would like the users to have the option of filling in the cells with "RED" by any means.

Here are the conditions or pseudo
If (any cell in Column X is RED)
Then (copy the row to Copy Rows Filled With Red)

If ( the cell in Column X is not longer RED)
Then (remove the row from Copy Rows Filled With Red)

In plain English, if the cell is filled with RED, it is copied over to sheet Copy Rows Filled With Red. Or if the cell is no longer filled with RED, it is removed from Copy Rows Filled With Red.

Please see attachment for explanation.

View Replies!   View Related
Copy And Paste A Row When A Cell Is Filled With The Color Red ..
I am trying to create an conditional statement using this code

View Replies!   View Related
If Any Cell Is (red) Has A Color Index Of 3, Bring The Whole Row To The Top
I have a worksheet with several columns and 1,000's of rows. I have code that makes all "good cells" grey (color index 15) and all "bad cells" red (color index 3).

I would like to do 2 things...

1. If ANY cell is RED, cut the WHOLE ROW and "insert cut cells" below the header row (even if ALL other cells are grey), then repeat the process up the whole worksheet until ANY row with a red cell is at the top.

2. Create a new worksheet named "Trouble Cells", copy the header row along with any rows with red cells.

I would like to keep the formatting the same (for example, the title row is always yellow and is "28" high and all other rows are a height of "12").

I would also like to keep the column width of each column in the new worksheet as well.

Excel 2002

View Replies!   View Related
Use Conditional Formatting To Turn Cell Red If Any Cells Above Are Blank
I have a formula in A56 that adds cells A1:A54. I want to use conditional formatting to highlight A56 if any of the cells in A1:A54 are blank. I am unsure how to do this.

View Replies!   View Related
Conditional Format Where If C3 Text=red Then A3 Text=red
How do i do a conditional format where if c3 text=red then a3 text=red

View Replies!   View Related
Run Macro Based On Result Of IF Function/Formula In Cell
how to run a macro from an IF function, if the function is true macro 1 runs if the function is false macro 2 runs.

View Replies!   View Related
Conditonal Formatting (cell Automattically Turn Red Once The Date =NOW() Is The 15th Sept)
I have worked out the conditional formatting but i have a question. The cell has a date which is input manually - no formulas or anything
it reads 14/09/09. however can i have the cell automattically turn red once the date =NOW() is the 15th sept??

View Replies!   View Related
VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.

Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.

View Replies!   View Related
Add Value '1' To Cell Based On Time
If there is a time value founded in column G.

How can i add in value "1" into specified column H(1000hr-2200hr) or I(2200hr-1000hr) based on the 24hr-time format in column C ?

View Replies!   View Related
Add Text Based On Part Of Another Cell Text
I have been working on this worksheet part of which is attached herewith. I would like excel to automatically enter Updated/Inserted in column B against Individuals' names as per the instructions given in column A. For example: As per instruction in A9, B13:B16 should show Updated. I have tried to use the nested if function, but it does not work as I want it to. Also as I am not used to macros or VBA codes, could this problem be solved with formulas?

View Replies!   View Related
Conditional Properties: If A Number In A Different Cell Is Above A Certain Number It Will Turn Green And Another Cell To Turn Red
can I set a cell in excel so that if a number in a different cell is above a certain number it will turn green and another cell to turn red. I have attached the file.

View Replies!   View Related
Call An Add-in Function From VB
I created a file with one sub and one function. Saved it as an add-in. and followed the promts to insert the Add-in.

The button I assigned to the macro works fine, but when I call the function, I am told "Sub or Function not defined".

Is there some special way to call an add-in function from VB?

View Replies!   View Related
Trying To Add Hours - If Function
I am trying to add four hours to a time. That I know how to do, but there is some added logic.

If I put a value in the first column I want to add logic to make the values in second column appear. It is basically adding four hours to a time value unless it is after 5pm, before 8am, etc. This partially works, but doesn't count for the 2pms, 3pms, 4pms, etc.


View Replies!   View Related
Add Cell Number To Date & Add Weeks
I want to add a numeric number eg: 4 to a date format eg: 15/08/2007 so that it calculates 4 WEEKS from 15/08/2007 and returns the CORRECT date in a date fomat itself. How do i do this through a VB code ?

View Replies!   View Related
Add Sum Function After Macro
I have found this code to spred my data from master worksheet to clients worksheets!

Is there a code to add the Sum function in the last row on each of the new worksheets in specific columns where is needed?

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("MAIN")
Set rng = Range("A:AG")

View Replies!   View Related
Add A Twist To The SumIf-function
I want to add a twist to the sumIf-function, and need some help. My request is similar to lots of other sumIf-issues posted here, which were resolved with the sumproduct-function, but is a little bit different.

View Replies!   View Related
Looking For A Function To Add Continious String
I am trying to make a function to take 3 Cells and using the initial Cell(say A1 with value of 100) as the inital value of a sum. Take Cell two(say A2 with value of 10) and add the value of A1 to Value of A2 in a constant increase. In otherwords A1+A2*A3= next increase. A3 is the value I want to change. But I want to add these values together as many times as the value of A3. so with current numbers will be 100+110+120+130+140 with A3 being a value of 5.

View Replies!   View Related
Find & Add Function
I'm looking for something simple that will let me type in a 5 digit numeric value and then when it finds the associated value, will add +1 to the associated col/row.

Such as, type 11111 in the box, click the button...When it finds the data on say row 8, col A it will then add +1 to row 8, col G.

I'm not sure of the best way to do this...I'm not exactly new to excel, but it has been a VERY long time (10 years!) since I've used it in this aspect...Just trying to help a friend out.

View Replies!   View Related
Add An Ontime Function To A Macro
I'm using this to unhide rows one at a time:

View Replies!   View Related
Add A Function To Personal.xls
I have a function saved in my Personal.xls (attached) but it is returning a #NAME error when used in other sheets. I am guessing that the NAME error means that the function can't be seen by other excel woorkbooks. I have already checked the following with no result:

1. Testing the code in a different workbook.
2. The code is visible in the script editor, but personal.xls is open but hidden.
3. When I open Tools>Macro>Macros the option of "All Open workbooks" is selected from the drop downbox.
4. Macro security is on low.
5. saving the function as a .xla file and running as an add-in

View Replies!   View Related
Calling Function From Add-in Causes Crash
I have an add-in that has one function in it. We plan on adding more, but not until we figure out what's happening. The one function is a Select- Case statement...send the function an alpha code and it returns a numeric account number based on the alpha. About 20 "cases" in total. The add-in loads fine and the function works fine from a spreadsheet cell. However, we're getting differing results when we try to access the function from VBA code. I've been able to duplicate this several times on three different computers on our network.

The VBA code will work fine, several times and then on the 4th or 5th attempt it will cause Excel to crash ==> "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience." If I " recover" the crashed file, comment out the line to call the function, run the program again it works. Then I can un-comment the line, re-run the program and it works fine again for 4 or 5 more attempts. I've tried running the Repair tool already. I've also copied the code out of the spreadsheet and started a new file. The same errors 4 or 5 times and then crashes the program. The add-in and the spreadsheet exists on networked drives.

View Replies!   View Related
Format Date Add Function
I am trying to format this function to just return the year, but I can't seem to get it to work. It just returns the full date and all I need is the year.

DateAdd("yyyy", -1, Date)

View Replies!   View Related
Custom Function Preceded With Add-in Path
I have created an addin (myAddin.xla) with custom functions in it. In a given cell, my funtion would look something like "=myCustomFunction(A1, B5)".

Periodically, while I'm working with the spreadsheet, I'll do something to a cell and the formulas in all the cells which used a custom function immediately get reset to something along the lines of "=C:...Application DataMicrosoftAddInsmyAddin.xla'!myCustomFunction(A1, B5)". The reset function also causes the function not to work properly and I get a "#NAME?" error in the cell.

I haven't yet figured out what sequence of actions causes the reset (it happens infrequently, but often enough to be a major problem) but it happens while I'm working with the document (i.e., it doesn't seem to be linked to saving/closing/reopening/etc). After I notice the reset, if I go to each cell that uses a custom function and delete everything from "C:... to myAddin.xla!" in the formula bar and hit enter, the function works normally as before.

(Technically, my Windows XP Pro system uses synchronization with a server at work, so the file path is not "C:...Application DataMicrosoftAddInsmyAddin.xla'!" but rather "\myServerNamemyDirectoryDocuments and SettingsmyUserNameApplicationDataMicrosoftAddInsmyAddin.xla'!". I'm not sure if this reset error has anything to do with being connected to the server or not, but figured I'd mention it anyway.)

View Replies!   View Related
Using Add-in For User Defined Function - Speed
I just hit F5 and use it in Excel with no problem. This time a person who developed the function had me install it as Add-In. I do not know if it is supposed to give any advantages (versus simply typing function in VBE window + activating it by hitting F5 key), but the function works extremely slowly. Is this Add-In

View Replies!   View Related
Call Add-in .xla Function From Workbook
I have created an Excel add-in (.xla) which contains many custom functions. This add-in is stored as read-only on a network drive. I reference it from various spreadsheets and calls to it work perfectly unless I put a call to it in the Workbook_Open event. When I make a call to a function in the add-in from a spreadsheet's Workbook_Open event, Excel crashes completely and I get the dialog that offers to recover the file and report the problem (i.e. Excel shuts down). I have no doubt that this is being caused by the call to the add-in specifically from the Workbook_Open event as removing this call solves the problem. Also, it is not related to a particular function as I have tried various ones; it is ANY call to the add-in from Workbook_Open that causes it. Is this a bug in Excel or is there something that I need to do before referencing the add-in in Workbook_Open? I have tried Application.CalculateFull and Doevents prior to making the call to the add-in and neither approach worked.

View Replies!   View Related
Add Vlookup Function To Range With Macro
I wanna have a Vlookup Macro function in my spreadsheet. I perform a vlookup in the spreadsheet by using vlookup formula and I found out that it is waste of time to retrieve records from other file when I have a total of 55000 records in one spreadsheet. I plan to use macro functions to retrieve records instead of using vlookup formula. So is there any chance that I can create the macro based on my scenario? {i already attached my spreadsheet here, it is a dump data}

In the sample1.xls, I want to retrieve the records from the sample2.xls by using Material as the key field. When I run the Vlookup Macro, the system is able to help me lookup the value such as (Net Sales: January until Net Sales: March) and display the records in the sample1.xls based on the Material Key Field. So hope that my problems can be solve here.

View Replies!   View Related
Add Ranges In VBA To Worksheet Function
VBA will not allow the worksheet function MMult to be called during the subroutine. The error that flashes is: "Run-time error 1004. Unable to get the MMult property of the WorksheetFunction property"

Worksheets("MatrixOutput"). Range("ak4:di35").Select
XTranspX = Application.WorksheetFunction.MMult("c4:ah80", "ak4:di35")
Worksheets("MatrixOutput").Range("b84:ag115").FormulaArray = XTranspX

View Replies!   View Related
Matching Names On Different Worksheets With Function To Add Abbreviations
writing a macro script. I want to find matching names on two diffferent worksheets. More specifically I need the ability to match if I apply a abbreviation(s) to worksheet one in order to get matches in worksheet two.

e.g Company = Co. or
Insurance = Ins

Please see my attached excel document for an example.

I have over 27000 lines so doing this manually will take forever!

Ideally I would like this to be a macro script that I can edit to add as many different abbreviations as required. The attached document will make my task a lot clear.

Unfortunately I don't have any starting code as I am not an excel/visual basic programmer, But i hope somebody will be able to help me out here.

The reason I need to do it this way is because I have been given a file from a partner company that uses different abbreviations to the ones we use in my company. I want to see if the companies in the list I have been given already exist in out company database, therefore we need a suitable matching procedure.

View Replies!   View Related
Copyright © 2005-08, All rights reserved