How would I make cells in column A to C be highlighted yellow, of which is 0.02 in value be higher than in compared with the adjacent cell in column D ? It is easy to use condition format function to setup the conditions in 1 row. The problem is there are thousands rows in the sheet(of which represents an individual file by each row), and need to have the conditioning format applied on each row independently. How can it be done w/o the need to have format painter applied line by line, of which is a very time consuming.
I wish to set 2 different cases (good and normal sales) for a list of data.
During good sales - fruits can be sold within 4 hours - Fruits sold off during peak season (Definition of peak season: 2 calendar days before or on public holiday or 10 days before 31st May) - < 2 fruits per type
Date of public holiday for example: 1 Jan 2014, 18 Apr 2014, 1 May 2014
For normal sales - fruits only able to sell of the next day - Sell of on non-peak season - > 2 fruits per type
I have a column in a spreadsheet that tells me when we ask for a check. I would like to take another column which has the date of when we receive the check to change colors based on whether it is within 30 days of the "ask for check" column and then another color if it is over 30 days. Let me try to do an example below:
ask for check Date Received 1-10-12 1-30-12 would be blue because it before 30 days 1-1-12 3-1-12 Would be red becaue its over
1.- I have in any cell the name of the month ( example :A1 )
2.- In other cell I have to put this words . . " YES" , ( example B02).
3.- I need the cell "A1" fill with a color a) if in the cell "B2" dont have any word... then FILL the cell "A1" with Yellow color b) bu if in the cell "B2" I put the word "YES" then do nothing with the cell "A1"
Is there any way I can use conditioning formatting on datapoints in a graph, so that if certain points meet the criteria they will appear with another symbol?
Suppose I would like to change symbol on the datapoints exceeding the value 200 in this graph: Graph.jpg
I wish to perform different actions depending on whether certain errors are present in a cell and I can't figure out how to do it.
Attempt (within some For loop):
othCell = ActiveCell.Offset(-95 - counter, 0) If othCell = CVErr(xlErrNum) Then conditioncount = 0
This generates a "type mismatch" error. I also tried othCell.value in place of othCell on the second line; I don't really follow the distinction in usage for "value". I've also attempted this with Error 2036 in place of CVErr(xlErrNum). I don't know much at all about VBA!
Im working on a large workbook with multiple worksheets. Each of these worksheets has a large amount of data relating to training records. In particular, the date that an employees certification is due to expire (eg: 01/02/2007 or 31/09/2009 etc)...(im also in Australia so my date formats may be different).
I need to color code these records with the below logic, eg:
If Cell Date is <Today() = Red Text or Cell If Cell Date is <18months from Today()+1 = Yellow Text or Cell If Cell Date is >18months from Today() = No change - leave white
I have basic VBA knowledge, but will be able to work my way through some of the code that is posted.
It's also key to note that there are multiple 'Blank' and 'Text' Cells in these worksheets. I only want to apply this code to a cell if it is populated with a date, is this possible ?? (eg: i only want to change the color of the dates listed, not the other cells with text and single numbers).
I have played around with the Conditional Formatting but i will need more than 3 conditions in the future. I've also attached a small screenshot of the data for your convenience.
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.
How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09
I need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.
I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
I have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I'm trying to format my email generated of excel sheet to have an HTML formal before they are sent out using MS Outlook.
I have googled, searched your forums and also tried to figure out a solution with the existing helps on the internet. I need help on how to format emails genarated out of my excel sheet. I know a bit of HTML syntax but a newbie to VBA. I'm guessing the formatting has to be done in the Emailbody text function in my code.
I'm also trying to understand the logic behind the code and also learning excel vba bit by bit at the minute.
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
I have a some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:
Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result.These are the type of dates:
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
I need in changing the format of sheet2 to exactly like the following image below. *there are some codes in sheet2 which I think is the place to change the format.
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another
18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK
I have a spreadsheet which needs to display currency in the correct format dependant upon whether a contract is in English or German Law. Excel defaults currency format as €310,000.00 however in German this needs to be €310.000,00 i.e. with the decimal point and comma in opposite positions. Does anyone know what expression I can use to format it as the latter?
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.