how to combine 2 conditional formats?
I'm trying to shade every other row grey so its readable with this:
=MOD(ROW(),2)
But, at the same time, would like to highlight upcoming expiry dates with this:
=AND(A2-TODAY()>=0,A2-TODAY()<=30)
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.
Anyone figure out how to get around the 3 format limit for conditional formatting? If so, I'd really love to hear about how you did it.
View 9 Replies View RelatedI 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 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.
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.
View 8 Replies View RelatedI 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.
Here's my problem..
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
See my attachment.. sample prob.xlsx
apply conditional formats to a cell, based on the entries in another, different cell?
View 2 Replies View RelatedConditional Formating Text equaling to Number formats:
Example:
C20 is '436.59 (its pasted as text often is must remain as text) and D20 is 436.9535
I want D20 to have only 2 decimals and turn green if it equals C20. And turn red if it doesnt equal C20.
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?
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 am trying to achieve old products on a database that we no longer have any stock of and we haven't sold since a specific date. I am having probems with an "=if(and" formula. At least i thin it is an equals if and formula that i need.
Basically i need to say the following:
=if(Stock quantity column = 0 and the date field is <= 31/12/2005 then "Delete" or "Keep")
I would like to combine a formula with conditional format:
In P10
If I4 = "FPI/FPI" and P10 = "Y", then the cell pattern is a different color.
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.
View 4 Replies View RelatedThis error occcurs when i add some worksheets to a workbook from another
one. I am not completely sure (cos this is not my work actually) but it
seems to me that there is not really too much (about 4000?) "different" cell
formats in the workbook, but there is a quite lot amount of drawing objects
(grouped technical drwaings plus autocad objects which i also converted them
to bitmaps to overcome the error).
I also dont understand the restriction:
If i have 3999 cells formatted "bold" and another 2 formatted "underlined"
this should not count 4001. True?
My workbook has about 15 worksheets with each fits to 2 printing pages.
I'm running excel 2000 and don't have access to mscal.ocx, so i have used the date time picker with a calendar, but the formatting of the calendar is in US format m/d/yy and I need it in UK format d/m/yy, I have narrowed it down the following piece of code, the D values are the days,
View 12 Replies View RelatedI need for my office a table which has one column with dates. The "issue" is, that sometimes I know the whole date (day, month, year), sometimes only the month and year and occasionally just the year. Is there a way through custom number formatting that excel behaves correctly? How would I need to set conditions to achieve this? Because now something happens which is 99% incorrect .
View 6 Replies View RelatedI have data sent to me with different date formats on the same spreadsheet; I used Format - Cell and format date to this format: yyyy.mm.dd. However, only some of the data changed to this format and rest remain the same. I've tried many other ways, but didn't work.
View 14 Replies View RelatedI need to create a custom number format for a recorded delivery number in an excel sheet. I want it to look like AA 1234 1234 1GB in the cell even if the user enters it without spaces e.g. AA123412341GB. I have had a bit of a play around with underscores to get spaces but nothing seems to make it change.
View 4 Replies View RelatedI'm trying to put cemetery records in a simple, sortable table, names and dates. The problem is that for some entries I have a full date of birth, death, or burial (e.g. 5/12/1892) and for others only a year (e.g. 1892). Is it possible to sort these? All I can get is the just years in order then the full dates at the end. My data look sort of like this:
Smith John 1892 1/1/1940 2/2/1892
Johnson Sarah 3/12/1900 1880 3/10/1900
One thing I don't know is what number format to use (text, long date, short date....) Sometimes the date I type changes after I type it (like 1892 became March 6, 1905) and sometimes it doesn't even though the cell is the same format.
I have slight problem with an OnChange Event by Target.
I am looking to be able to change a cell value from one to another, create a comment block that says "person B for person A" , change the color and be done.
If the cell is empty then the code should exit the sub (not working). If the cell has already been change once then I want the comment box to show a second line of who changed what.
What I tried in the comment box, thinking it would keep the value:
