if statement to change color of text

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Worksheet
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Basic question - how to do an average I need to average a set of figures in column A. my formulas not working - i get the name error. the figures are in A2 to A22. Stupid question but how do I calculate the average for this figures in a formula any help would be great tx suzanne Tag: if statement to change color of text Tag: 497473
  - 2
    - copy if...or transfer if Dear all i have in sheet 1 a list of 3500 acounts where i have make clear of important and non important acounts that i need, by give them a simple color to the specific cell. Because there nothing to short them by color, at least i have not found anything yet, there is any way to use a formula or a vlookup to copy let say all the cells with yellow colour to sheet 2? I want to split the accounts by the color i have provide to the cells. Any ideas? Thanks in advance Manos Tag: if statement to change color of text Tag: 497468
  - 3
    - How do I convert from a julian date to a standard date mm/dd/yyy can someone give me the formula for converting from a julian date to a standard mm/dd/yy year formt Tag: if statement to change color of text Tag: 497450
  - 4
    - Extract a text string based on character I am trying to extract a model number from the title. Brand New D-Link AirPlus G DI-524 Wireless Router; I need to extract the DI-524. All of the model numbers have the - charcter, but are different lengths. How would I use the mid function, not knowing the length and exact position? For instance, here is another title: **New** Acer TravelMate TM4720-6218, 2.2GHz Core 2 Duo; I need the TM4720-6218 extracted from the text string. Thank you for your help! Tag: if statement to change color of text Tag: 497445
  - 5
    - Vlookup? populate 3 columns of data when match a common value, I have a worksheet with 35,000 row accros 10 colums with 1 column of phy-care giver number (text but it think I can convert it to a number) and in a separate table I have 3 columns of a list of 2,000 phy with the same caregiver number. I would like to match to the lareger data sheet with 3 columns of data on the same row containing Division, subspecialty, and name. I think rather that vlookup I would like to make a formula to reference the caregiver# in the larger data set to the same caregiver number of the smaller phy list then if match populate the next 3 colums of division, subspecialty, and name to the larger sheet. Thanks Tom. Tag: if statement to change color of text Tag: 497443
  - 6
    - 'Flash' random numbers Is it possible to have numbers from a predetermined list, appear in a specified cell at a specified time interval? IE from a list in (A1:A30) containing numbers from 1-30, I would like a random number to appear in F10 every 4 seconds. What do you think? -- Traa Dy Liooar Jock Tag: if statement to change color of text Tag: 497442
  - 7
    - IF THEN in cell? I am taking over a spreadsheet that has a list of options that is specific to one name in it. Currently when you choose this guy's name, the user has to hunt and peck for the choices that are specific to him. Is there a way that I can force Excel to display just those choices when his name is chosen? Tag: if statement to change color of text Tag: 497436
  - 8
    - COUNTIF question With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a space after the final letter are, quite correctly, treated as a different word and therefore not included in the total for a particular word. IE: [testing] and [testing ] are treated as different words. (brackets to show the space) How can I adapt the formula to ignore any space after the final letter in the cell? Thanks, -- Traa Dy Liooar Jock Tag: if statement to change color of text Tag: 497435
  - 9
    - How do I enter degrees-minutes-seconds in a cell? I need to add several angle measurements in dd-mm-ss format. How do I enter these into excel cells and have them work? I saw postings from a year ago but couldn't get it to work. Office excel 2003 Tag: if statement to change color of text Tag: 497428
  - 10
    - Active Cell Border Color I know this can be done, I just can remember how or seem to be able to find it under help. I would like the border around my active cell to be a different color so it stands out easier than the now black border on an all black text document. I know how to add borders to cells, but I need the active cell border than moves to change color. Please help if you know how to do this! Thanks. Tag: if statement to change color of text Tag: 497408
  - 11
    - Can I randomize numbers ALREADY in a column? I have three columns of numbers Column A = 9101-9120, Column B = 9121-9140, and Column C = 9141-9160. I need to randomize columns B & C seperately, but with the numbers that are already in the columns. Can I do this somehow? Tag: if statement to change color of text Tag: 497405
  - 12
    - Formula Why do I have to click the formula line every time to get the cell to update. I put in the numbers and the cell with the formula does not update until I click on the formula line. How do I fix this so it updates automatically? Tag: if statement to change color of text Tag: 497402
  - 13
    - Sorting in a Spreadsheet with Filter I have a spreadsheet for work orders. The columns are WO#, Date, Planner, Vendor, Bldg, Room, Equip#, Date Entered, PO#. Each Planner uses the automatic filter to see the line items that belong to them. They then would like to be able to sort by vendor alphabetically. This is the part that's not working. It seems to be mixing up the lines. Can someone please help? Thanks. Tag: if statement to change color of text Tag: 497400
  - 14
    - Using A Function in a Macro Good Afternoon All, I have a function built that takes a character based coded cell value and reformats into a number with a formula. The function works fine manually using it example =BigBookingNumber(cell value) However is it easy to create a macro that read sa specific column and runs the function where there is a value (And also replace the original value) So in a sense it take each value and puts =BigBookingNumber() around it Thanks in Advance Jai Tag: if statement to change color of text Tag: 497399
  - 15
    - set up macro to goto cell with "today" in I have mutiple worksheets with daily information covering 6 months. I have set up formula that highlights column with "today" realting to current date. I would like to set up macro on each worksheet that I can run to take me to "todays" date Tag: if statement to change color of text Tag: 497397
  - 16
    - extract identical rows from 2 excel file While it may be quite easy to achieve in SQL to extract identical rows from 2 files, is it possible to do that in Excel (The last thing to do is to merge them and sort?) Tag: if statement to change color of text Tag: 497396
  - 17
    - How to create a macro in excel to use in different workbookks? Need to create macro in excel to use in different workbooks and it seems that when I recorded the macro it was only looking at the current workbook. How do I create it so that it run on all workbooks? Tag: if statement to change color of text Tag: 497392
  - 18
    - How To Detect A Negative Number In A Cell Greetings, I am trying to detect whether the value in cell M for each row has a negative number. If it does I need to change the text color for that row to red. Any help, insights or links would be greatly appreciated. -Minitman Tag: if statement to change color of text Tag: 497390
  - 19
    - Character Search a cell In Excel I need to character search a cell and determine if one of 3 text strings, 01-08, 08-07, 27B can be found anywhere in the cell, if one of them are found I need UPDATED to show in the results cell, if none of then are found the results cell will be blank. -- JPS Tag: if statement to change color of text Tag: 497388
  - 20
    - sorting a column I have four columns on my sheet but I only want to sort one of them. When I clicked that column's header to sort it but the "sort warning" pops up, saying that there's data next to my selection. I tried to "continue with the current selection," but everything turns into zero in that column! Please help! It'll be greatly appreciated! Tag: if statement to change color of text Tag: 497387
  - 21
    - Why does Excel always crash when I try to change to font colour? Font color of some numbers are currently defaulted to Red. When I highlight this cell, then right-click the button that gives the drop down menu to change the color of the font, Excel crashes. I have Windows Vista and am using Office 2007. Tag: if statement to change color of text Tag: 497381
  - 22
    - SUMIF with Multiple Conditions I'm new to the community here so bare with me if my lingo isn't up to par just yet. I'm in Excel 2003. My workbook has 2 sheets; 'Inventory' and 'Source'. 'Inventory' has: Month SKU# Beginning Inventory Ending Inventory Jan '08 555 50,000 444 35,000 333 40,000 'Source' has: Order Date SKU# Quantity 01/13/08 555 25,000 01/22/08 444 10,000 01/22/08 444 3,000 01/22/08 333 5,000 01/31/2008 555 3,000 02/13/08 555 2,000 02/22/08 333 1,500 I'm needing to provide the 'Ending Inventory' to the "Inventory" worksheet, using the beginning quantity (in 'Inventory'), subtracting the total quantity for each month (In 'Source'), by SKU #. In my terms, the Ending Inventory for Jan '08, for SKU # 555 above would be: Beginning Inventory shows 50,000 If SKU = 555 & Date is between 01/01/2008:01/31/2008 = 28,000 If SKU = 444 & Date is between 01/01/2008:01/31/2008 = 13,000 It's the 'between this date range' that is throwing me off and then coupling it with the SKU #s. Any direction is much appreciated. Truly! thnx! Tag: if statement to change color of text Tag: 497379
  - 23
    - unprotect sheet & workbook dim out Hi I have created a shared workbook, protect 5 individual sheets in the workbook and allow some users to update sales data only while protect formulas. I have also assigned a routing recipient function to the workbook, then it finally comes back to me after all of updates. However I can not unprotect sheets and this workbook any more, because the unprotect functions for the sheets and workbook just dimmed out, urgent help! many thanks... Tag: if statement to change color of text Tag: 497376
  - 24
    - How to Sum Time in Hours & Minutes Hi all, I run a dayhome and am trying to make my life easier on myself with my attendance sheet but can't figure out why the formula wont work. I am trying to get the sum of hours and minutes each child is here during a month so I can easily figure out what to charge the parents. The problem I seem to be having is the total is always more than 24 hours so if I use the formula =SUM(D14:D20) the total only comes up as 0:00:00. I believe I read that, that specific formula is only good when the total is less than 24 hours. Hopefully this is making sense, does anyone know how to make this work? Thanks Tag: if statement to change color of text Tag: 497375
  - 25
    - Is excel capable of gouping numbers within 1 of each other I am trying to pick numbers out pf cells that are within 1 of each other, eg 4,5,7,9,1,11. I want 4,5 to go together. Tag: if statement to change color of text Tag: 497373
- Next
  - 1
    - time formats when working out a time sheet for example, i want to be able to enter time using a decimal point (.) instead of a colan(:) and be able to add them together to get decimal hours. ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25 (7:15) Tag: if statement to change color of text Tag: 497358
  - 2
    - time formats when working out a time sheet for example, i want to be able to enter time using a decimal point (.) instead of a colan(:) and be able to add them together to get decimal hours. ie. start 8.00 (8:00) finish 3.45 (15:45) = 7.75 (7:45) Tag: if statement to change color of text Tag: 497356
  - 3
    - select date in date range I want to be able to do the following: A B 1 26/03/2006 $20.00 2 1/12/2006 $40.00 3 1/10/2007 $100.00 4 5 6 7 28/11/2006 2 8 29/11/2006 2 I need a formula that says if the date in A7 is between date in A1 to A2, then muliply B7 by B1, else if the date in A7 is between date in A2 to A3, then multiply B7 by B2, and so on. Tag: if statement to change color of text Tag: 497347
  - 4
    - date range excel spread sheet Tag: if statement to change color of text Tag: 497345
  - 5
    - Compare One Cell to the First 10 Characters of Another Cell I would like to compare one cell (A1) to the first 10 characters in a range of cells (B1:B125). If there is a match, I would like it displayed in C1. Any help would be appreciated. -- Jill Tag: if statement to change color of text Tag: 497335
  - 6
    - Filter in Excel How do I remove a filter that is frozen? Tag: if statement to change color of text Tag: 497329
  - 7
    - moving data from a form to another sheet I have data on a form that I need moved to another sheet based on a product name. How do I do this Tag: if statement to change color of text Tag: 497325
  - 8
    - Should I use VLOOKUP? IF? INDEX? I have a column in a large spreadsheet called "warehouse". "Warehouse" (A2 thru A7500) can be one of 25 possible names (MOreturns, MOinv, MOjunk, TXreturns, TX2returns, TXjunk, AZreturns, AZ2, etc.). I am inserting a column (B) where I want the warehouse location to populate unless it is a "junk" warehouse, i.e. if A2=MOreturns, B2=MO; if A3=TXreturns, B3=TX; if A4=AZjunk, B4=blank... I tried using INDEX to match a name list but since it's not a 1:1 relationship, I couldn't get it to work and for some reason my nested IF function came back saying one of my values was the wrong data type (I enclosed the text in " " so I'm not sure what the "wrong type" was). Would a VLOOKUP function work best or a nested IF function? Please give an example of the formula. Please be specific. Thanks! Tag: if statement to change color of text Tag: 497314
  - 9
    - a formula that returns the previous fridays date I need a date formula that returns the date of the friday from the week before. For example i need it to return March 07 2008 up untill March 14 2008 the it will return that untill March 28th and so on? Any help would be greatly appreciated Tag: if statement to change color of text Tag: 497309
  - 10
    - Help with worksheet code This is almost what I need but can anyone help please. I need for it to COPY "Sent to Assembly" sheet each time something is added(Columns A, B, C and sent to Sheet1. Right now it only copies Column A. Also should this go in a Module or a worksheet function? Thanks in advance Tag: if statement to change color of text Tag: 497303
  - 11
    - Evaluating mulitple criteria with COUNTIF Can anyone help me with a formula to count the number of occurrences a color appears for a particular location? Sample Data: Location Color 2242 ALMOND 2247 ALMOND 2247 WHITE 2247 WHITE 2247 BLACK 2242 BLACK 2242 WHITE 2242 ALMOND 2242 BLACK 2242 ALMOND Example Result: Location ALMOND WHITE BLACK 2242 3 1 2 2247 1 2 1 Thanks. Tag: if statement to change color of text Tag: 497302
  - 12
    - how to apply unique constraint in excel column how to apply unique constraint in excel column Tag: if statement to change color of text Tag: 497296
  - 13
    - Inserting Multiple Rows Throughout Worksheet Is there a way I can insert rows throughout a worksheet without having to do it individually? Tag: if statement to change color of text Tag: 497293
  - 14
    - sum formulas based on date How do I create a formula that will sum up a row based upon a particular date? -- Jia Eclipser Tag: if statement to change color of text Tag: 497290
  - 15
    - Excel Hyper Links I have a column that has a list of paths and file names. I need these to be turned into hyperlinks to the file listed. I cannot however use the =Hyperlink formula, because when I do that the link does not come through when i covert the spread sheet to a PDF, it only seems to work when I insert the hyperlink. Please tell me there is a way to do this. Tag: if statement to change color of text Tag: 497289
  - 16
    - Search for multiple criteria, and return yet another Basic output from 3rd party database is .xls in a format similar to: Name OtherData OtherData EventName PointsEarned Jones xxxxx xxxxx baseball 2.0 Jones xxxxx xxxxx football 2.5 Jones xxxxx xxxxx soccer 3.0 Smith xxxxx xxxxx baseball 1.0 Smith xxxxx xxxxx football 1.1 Smith xxxxx xxxxx soccer 1.5 White...and so on My Goal: Lookup by Name and EventName, and return PointsEarned. BASEBALL FOOTBALL SOCCER Jones 2.0 2.5 3.0 Smith 1.0 1.1 1.5 White... I also totally lack understanding of PivotTables, and out of pure stubbornness, am trying to avoid using them. ...probably to my own demise. Thanks in advance. -- JMG Tag: if statement to change color of text Tag: 497283
  - 17
    - HLOOKUP copied across rows with "row index number" changing I have a workbook that I use to keep track of individulas time worked on specific jobs. One sheet per person. On anygiven sheet, I have a list of jobs in Column A and hours worked on these jobs entered in following columns with the "weeks end" date as the column heading. At the end of each month I need to invoice and wanted to see the totals for a selected month. Because of the nesting limits - I could not use the IF function. So here is how I went about it: I created a range off to the far right of my sheet (which will be hidden). Headings are the months - January thru December. Under those months - I sum the columns for the given month relative to the job in column A. I want to see month totals in Column B. B1 contains the drop down list where I choose the month I want to see. Starting in B5, I entered =HLOOKUP($B$1,$BV$2:$CG$65536,4) where 4 is the same row as B5 where I entered the formula. Now I want to copy the formula down the rows with that "4" (row index number) increaseing as I go. Thanks, Tag: if statement to change color of text Tag: 497266
  - 18
    - Making formula too complicated? Here's the format of the columns/cells I'm working with: col X Y Z AA AB | Qualified | Day | NBC | Night | M203 | In the Qualified column it should be either GO or NO GO The other 4 columns are dates when the event occurs I originally didn't have col AB and the formula was: =IF($B12=""."".IF(Y12="NA","NA",IF(COUNT(Y12:AA12)=3,"GO","NO GO") Col B is where the soldier's name is entered so if there no name Col X is left blank. I had no problems with this formula; however, I now added col AB and now have to make a distinction between the two weapon systems. In col I12 there's a selection of weapons (M4/16, M203, and M9). The formula I had for col X was: =IF($B12=""."".(Y12="NA","NA",IF(I12="M4/16",COUNT(Y12:AA12)=3,"GO","NO GO", IF(I12="M203",COUNT(Y12:AB12)=4,"GO","NO GO"))) I get an error with the GO in the first "NO GO" Any suggestions? Tag: if statement to change color of text Tag: 497265
  - 19
    - Displaying a decimal as years and months Hi - need some help here... Does anybody know how to display a number as years and months? I would like to show 1.8 as 1 year, 9 months instead of a decimal. The 1.8 I get by subrtracting my start date from today's date.... =today()-Cell with start date/365 Tag: if statement to change color of text Tag: 497263
  - 20
    - If, Then Color Cell If the date in cell C3 is after date in cell C1, then color cell orange. If this is possible then can I do this: If the date in cell C3 is after the date in cell C1, then color cell orange and If the date in cell C3 is before the date in cell C1, then color the cell light blue. Thank you. Tag: if statement to change color of text Tag: 497255
  - 21
    - formula not working right I have a formula in my spreadsheet that check a balance. =IF(SUM(C17:H17)=I17,"OK", "ERR") Here are the values that are in each cell C17 104.86 D17 58.32 E18 -209.11 F19 0 G19 0 H19 52.78 I17 6.85 I get "ERR" because when I evaluate K17 where the formula is it gives me 6.84999999999999. I have tried everything I can think of to fix this with no luck. I even tried a new spreadsheet with the values and fromulas with no luck (I did not use copy and paste to get the info to the new sheet, typed it all in) Please help Tag: if statement to change color of text Tag: 497253
  - 22
    - Cell Reference confusion I am trying to apply a conditional formatting rule that would return a true if the cell immediately to the left does not have a value exactly 1 less than the value of "this cell." My problem is that I can't find a function or reference operator for "this cell" when applied to an entire column. ie- A B C 1 2 5 6 True 3 12 13 True 4 6 5 False 5 7 9 False Conditional formatting applied to column B. I looked at offset, but I need an absolute cell reference to offset from, and I don't have that. Thanks in advance Tag: if statement to change color of text Tag: 497252
  - 23
    - Automaticly changing average function target cells I am looking to create a function to only average the last 6 entries in a list of numbers w/out changing the target cells of the AVERAGE function on a monthly bases. Example: I want to track scores on a monthly bases but only want the average of the last six months worth of scores. Tag: if statement to change color of text Tag: 497250
  - 24
    - Can I set up an excel doc to auto fill 27 days in next cell? I am trying to create a spreadsheet where I can enter a date and have a date 27 days from that date pop into the cell next to it. I need to do this for 7 months. I don't even know if it's possible. For example, if I put the date 10/01/2008 into a cell, I want the spreadsheet to calculate 27 days and put that date into the next cell (10/28/2008). I want it to do that for 7 cells. Tag: if statement to change color of text Tag: 497239
  - 25
    - Automatically adjusting # of entries by adding/removing rows Hello. I am looking for a solution to remove extra, or add more lines as required on a worksheet. Information is gathered from several worksheets which varies from week to week, and tabulating the information onto another spreadsheet. Is there a way of adding/removing lines automatically as required for a section of the spreadsheet, and still have the SUM feature adjust and work as well. â??Date/Activity/Amountâ?? is derived from other worksheets. The spreadsheet will be a template, the entries for each Item will vary from week to week, and each week is a separate spreadsheet. Week #1 Item 1 Total Amount: (SUM Amount Item 1) Date Activity Amount Date Activity Amount Item 2 Total Amount: (SUM Amount Item 2) Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Week #2 Item 1 Total Amount: (SUM Amount Item 1) Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Date Activity Amount Item 2 Total Amount: (SUM Amount Item 2) Date Activity Amount Date Activity Amount Date Activity Amount Thank you for your assistance. Tag: if statement to change color of text Tag: 497237

I need an if statement in cell a1 to change the text to red if cell f1 is
equal to zero. Can you help me?

Thanks,
Mike

RE: if statement to change color of text by GarysStudent

GarysStudent
Thu Mar 13 11:26:00 CDT 2008

1. Select cell A1 and pull-down:
Format > Conditional Formatting... > Formula Is >
=F1=0
and set the font color to red
--
Gary''s Student - gsnu200773

"mikesteven" wrote:

> I need an if statement in cell a1 to change the text to red if cell f1 is
> equal to zero. Can you help me?
>
> Thanks,
> Mike

Top