I use Excel 2003 and am trying to put together an automatic macro to apply five conditional formats to a range of cells. The range is H3:H21 and is unnamed. The values in the cells are derived from formulas. These are the conditional formats:
If there is a 1, the background color should be gold/44 and the font should be bolded.
If there is a 2, the background color should be gray 25%/15 and the font should be bolded.
If there is a 3, the background color should be dark yellow/12 and the font should be bolded.
If there is an e, the background color should be red/3 and the font should be bolded.
If there is a 0 (zero), the font should be white/2.
From what I understand, I need to put the code in by using the "View Code" option in the tab sub-menu.
The macro firstly formats the data that is pasted into it from an access database, then it does some calculations to determine when 10 working days from a specific date is, and when 20 working days from the date is (these go in new columns at the end of the data). The macro will also add new columns which say "Not resolved" if there is no date in the Resolution column, "Yes" in the "10 working days met?" column if the condition is met and the same for 20 workings days in a different column. As the colouring etc takes a long time I really wanted to add as conditional formatting to the macro!
how to attach a spreadsheet here then I can show you what the outcome we want is. A point to note is that there is not a set number of rows each time we do this, and I don't just want 1 cell to be coloured - I want to specify which cells in the row are coloured depending on the results in another cell on that row.
I have a spreadsheet with a number of columns. In column D there are the following options: 2,3,4,C,T. I want to color the cell if there is data in the cell and column D contains a value.
So for cell L17, my conditional format I have =AND(D17=2,L17<>"") - - (RED), =AND(D17=3,L17<>"") - - (YELLOW), =AND(D17=4,L17<>"") - - (GREEN).
I need to color for values of C and T: but there are only 3 conditional formats permitted. Is there any way around this?
I have a sheet that shows sun, mon, tue........,sat - all 7 days.
Under each day there are 3 cols so 21 in total.
Data is being entered into cells each day but as the week goes on it gets more difficult to match up the col & row, to many numbers.
What I would like is a way to highlight the whole range of cells per day in a different colour but only when data has been entered under a day, so if no data then no colour fill.
I only need 2 colours, 1 for sun, tue, thurs, sat & 1 for the remaining days.
=COUNTA($A1:$C20)>0
This works for one range as a conditional format but there aren't enough options to do the 7 days.
I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?
Eg. John has first aid and expires on 11/15/2009. I'd like the cell that has this date to highlight red on 10/15/2009.
I have messed around with the conditional formatting and know how to format the cells color and font et., but I can not figure out the formula to use to get my results.
I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)
I would like to condition one cell to display various colors, based on the information in another cell. I have attached the file for an example. The cell to be conditioned is A1, and the cell that will specify its color is B3. For example, if the value of B3 is "Red Oak", A1 would be red, and if the value is "Maple", A1 would be green.
I have more than 3 of these that need to be formatted (conditional formatting limits to 3 formats), so I'm wondering if I can do an event macro to set the formatting.
I have to color direct payoffs and commissions (green and blue respectively). I have to have a running total of payoffs that have not cleared and one for commissions. I have set up conditional formatting to color the text based on the check classification (A for payoffs and C for commissions). What I am trying to do is write a formula that will count only the payoffs (green text) that do not have a cleared date. I am then trying to do the same for the commissions (blue text). these totals are needed separately so I do not need them combined.
I have tried sumif and sumproduct. I have since deleted the formulas i have written cause they did not work.
I have a code that copies a template and pastes it into another tab. However, it is copying the conditional formatting and pasting it as well. I already have conditional formatting on the other tab and do not want it to paste another set of conditional formats because the formula's are changing between the copy and pastes.
i have a register with items for calibration. I have a column with a date the item is next due cal which is 3 years from date of receipt manually input by myself. say (H64) due 20/11/2010, i would like the cell to be green if todays date is below the date due, but amber if todays date is within 30 days of being due and finally red if todays date is over. i have manged green and red but cant seem to get the between right for the amber.
I have a spreadsheet containing all sorts of formulas & data in Columns A - H, and a formula copied down in Column I generates a number that drives a few conditional formats in the preceeding columns.
I'm trying to copy-paste the values and formats of Columns A - H into a new workbook with this pretty simple macro...
Range("A1:H195").Select Selection.Copy Workbooks.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False When the formats are pasted across it's pasting the conditional formatting, but they're not displaying correctly because Column I isn't coming over to the new workbook.
Is it possible to copy-paste the cell formats as they stand regardless of whether there's any conditional formatting behind it?
I need to count the number of rows of active clients. I have set up a conditional format that when someone goes inactive dependent on the reason of inactiveness their entire row of information either gets greyed out or a red line through it. Is there a way to count the current "active" clients- or those that do not have these conditional formats applied to them. And this is also dependent on month, so that it does not show the same number each month.
I'm trying to count cells affected by conditional formatting and I need help tidying up my efforts so far.
The numeric values in my table are formatted if they exceed values in corresponding columns on other tables, indexed via Hack #75. The logic for each cell runs someting like this:
if cell is numeric if column in (E, M, X, Y) if (value <c1 or > c2) highlight in blue end else ' columns not in (E, M, X, Y) if (value > c1 or < c2) highlight in red end end end
My attempt to put together a formula for each cell appears to work, it looks like this for cell B3: '=AND(ISNUMBER($B3), OR( AND(.....................
I have a range A1:D10 containing formulae. I want the cells to change colour depending on the formula result. Conditional Formatting will do the job but only up to three colour so I need to use VBA because I have 6 colours. For example, if the result is 1, the colour changes to blue, if the result is 2, the colour changes to green, etc
I have VBA code which will change the cell colour if I input a value, but not if the cell is relying on formula result. The formulae results are dependent on cells throughout the workbook. The formulae results can be letters or numbers, whatever is easier.
I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then Select Case Target Case "0.5", "1", "U" icolor = 38 Case "C", "M", "P" icolor = 40 Case "A", "S", "D" icolor = 36 Case "L", "UP", "C/E" icolor = 35 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub
* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).
* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.
* When the cell contains no value, the borders should return to how they were previously.
if i input 75 in cell G2, the value in A7:E7 will paste or will display in A2:E2 or if i input 76 in cell G2, the value in A8:E8 will paste or will display in A2:E2 or if i input 90 in cell G2, the value in A9:E9 will paste or will display in A2:E2 or if i input 78 in cell G2, the value in A10:E10 will copy or will display in A2:E2
I am using excel 2007 - I need the extra available columns in Excel 2007 for my application.
I have a spreadsheet that has a significant amount of conditional formatting. I want to copy a range from this spreadsheet to another spreadsheet. In the new spreadsheet I want to remove the conditional formatting rules whilst retaining the actual formats those rules generated.
Format painter and "paste special - format" copies any formats as well as the conditional format rules.
Is htere a method within Excel to do this or can someone suggest a workaround?
I have a cell with seven conditional formatting formula rules that I now want to copy to the rest of the column. I can copy/paste special/formats one cell at a time but if I try to to this with a group of cells, (or try using the format painter), it treats the formula references as absolute, even though they aren't shown as absolute in the rules manager. Am I missing something? Using 2007.
I receive sales data from my wholesalers every month and I continually have to format them to fit the structure of our in-house database. I wanted to design a macro that would automate this process. However, in some months, the files are recieved in a format that is a bit different from the wholesaler's usual format.
Is there such thing as an initial "litmus" test where I could try running the macro and if it doesn't fit the usual structure, there's an error code and I could do it by hand?
I did this macro which formats five worksheets in the same file. I saved this macro as a PERSONAL.XLS file. Last night when i open new files to re-run this macro it was working fine excep one of the worksheets wasnt formatting properly....the macro on this sheet wasnt working for some reason.
Then this morning when i opened new files and tried the macros again it wouldnt work at all. And there is no reacord of them I did five of them and they are all gone! I saved them so why did they disappear! I thought that when you saved a macro as a PERSONAL file that you could open new files and re-run that macro again and again????
An in-house server spits out a whole lot of dates which I put into Excel and then use the dates for analysis (VBA code). The problem is that Excel is getting confused about the date formats. Sometimes the dates are formatted as dd/mm/yyyy and then the next line can randomly be mm/dd/yyyy. This totally messes up all changes of having data integrity.
I have two columns containing dates (Date1 and Date2). Date1 is like a long date and Date 2 is a short date. I need a macro to compare these two dates and delete rows where Date1 <> Date2. Please find attached the before and after file which also contains the date formats for these two dates.
I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.
The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.
I must save: 1. Cell values 2. Cell formats incl merged cells, borders, colour, font etc. 3. The row and column sizes 4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)
I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.
The original file name is "Job Sheet - Omlaw.xls" The tab/sheet to save is "Front Sheet" (There are two other tabs - neither of which need saving.)
All cells to be changed to "locked".
The sheet must be password protected in Excel. User to enter it - or not.
The saved file should be "write protected" if possible?
I have a pivot chart, filtering data from a pivot table, the problem I've got stuck is that every time I filter the chart the colors and weights of the chart change to default values, not what I want, I've managed to every time I click on the pvt cht tab it updates with the colors and weights I want, but when filtering I don't know how to do.
I need to insert an Excel worksheet in the PACS (Picture Archiving and Communications System) in our medical imaging department. Our PACS only accept JPEG or TIFF or DICOM format. Currently, we convert the Excel sheet to PDF and then JPEG and then insert it in the PACS. Is there a way (macro? VBA?) to program a cell (lets call it "Save as a Picture") so that when I click on that cell, the worksheet get saved as jpeg or tiff or dicom format?
I have a workbook that has a lot of formulas in it that reference material stored on my local computer.
I need a macro that will copy all 42 tabs in to a new workbook book without all formulas, but saving all the formats have made. This would save me from every other day coping and pasting manually each of the pages.
