How to get "some specific holidays" highlighted by doing conditional formatting in series of "date columns"?? I got to know how to get "sunday" highlighted...but again problem with specific holidays...
View 9 Replies
Well I spent some time yesterday trying to figure this one out. It was only last week that I have taught myself how to use VBA in excel. However this looks like a though one.
Simply what i would like to do is highlight a column of a certain date (till a certain row number) based on a list of dates.
eg. Across the top I have a set of dates and on another sheet i have a list of dates.
I understand that there is a VBA code for such conditional formatting but i can't get my head around it.
In the attached, I want cells to be highlighted red if they're NOT EQUAL to 0. Why is this conditional formatting not working (i.e. the cell is being highlighted)?
Conditional Formatting Example.xlsx
I have been trying to find a way of formatting an entire row based on the contents of cells in each column. However i come unstuck when trying to make the column dynamic. Below is an example:
Month
2010
2011
2012
[Code]....
The idea is that i say current year is 2011 and all rows where C contains an a will turn green. What i would like to achieve is that when i change current year to say 2012 the conditional formatting adjusts so that it looks at D instead of C.
I want to change (via conditional formatting) the background colour of cell H64 and K72 when I select / highlighted / activate (i.e. just left click in the cell) cell C66.
I don't seem to be able to find in the formulae something to indicate if C66 is selected / highlighted /activated.
I have a spreadsheet where we track our contractor's Worker's Comp and General Liability insurance certificate expiration dates. I want the expiration date to highlight in red if it is expired and to highlight in orange if it will expire within 30 days or less.
I have attached a testing sheet similar to what I'm working on. The F column has the dates that need to highlight. I have MS Excel 2010.
I manage a number if individuals in the Army and as such, track their annual training requirements. Some requirements are semi-annual, annual, quadrennial, etc...
I have a spreadsheet to track the dates that the requirement was last completed (attached). I would like to use conditional formatting to turn dates that are within 30 days of being due (or blank) RED and dates that are between 31 days and 60 days of being due YELLOW.
I am using Excel MAC 2011.
I am trying to use conditional formatting to calculate when the date in the top column gets within 60 days from expiring to turn yellow, 30 days from expiring to turn orange and when it expires to turn red. The dates that are listed from row 5 down are when the classes were taken. the expiration date will be based on row 3. Some are annual,3 years and 5 years, etc. I am using Excel 2010....
View 10 Replies View RelatedHow to get "only Sunday" highlighted in a series of dates included in excel columns. I tried some formulas found from the web pages, but they all deal with both "Saturday and Sunday"...
View 6 Replies View RelatedI am trying to figure-out how to set up conditional formatting of dates in a column (e.g. Date Submitted) and have these dates, and/or cell, change color as time progresses. I have Excel 2007.
For instance I have an application submitted on 11/20/2011 in a cell. I would like to have the date change color (Yellow) after 30 days have passed.
Then do the same except change to Red after 60 days have passed the date.
I searched the forums and did not find this particular problem. I of course may have missed it too...
My guess was to try "$A$1
I am using Excel 2003. I have a spread sheet and I am trying to use conditional formatting. But I can't figure out how to use conditional formatting across multiple columns and so I have been doing each column individually which has been kind of a hassle. I also am trying to figure out how to put in a formula so that every occurrence of a certain text will show up a certain color. I know that I can just do "cell value is", "equal to" and click on the cell, but the information in this spread sheet is organized alphabetically and is going to be added to frequently therefore every time I add information it re-organizes. This then messes up my conditional formatting because the text value that was in the cell gets bumped up or down.
View 2 Replies View RelatedI'm using windows xp and excel 2010. I have a very large sheet that has a bunch of highlighted cells. There all highlighted the same color. I need to remove the highlighting colors and change the font color of the highlighted cells to red.
View 3 Replies View RelatedI'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
I am trying to do conditional formatting to make a cell say something for 3 different scenarios. If K6 > G6, then I need M6 to say "Early." If K6 < G6, then I need M6 to say "Late." If K6 = G6, then I need M6 to say "On Time."
View 7 Replies View RelatedIn my attached file,
In Column Q, Please drive a formula that shows.... after 7 days of column (M)reminder III these words must appear '' File Pending'' in 'COLUMN Q' and at the same, Indicator column (O) will also become RED.
Test.xlsx
The scenario. Column A has a set (due) date all of the same. Column B is where review dates are typed in. Column C is where the status is, Due = still as yet to be done, but still yet to pass the Due date with no date in Column B (formatted yellow with the text "Due"). Complete = for when a date has been entered in Column B (formatted green with the text "Complete"). Urgent = the review date has passed and with no date in Column B (formatted red with the text "Urgent"). I have tried by editing previous similar formulas but to no avail
View 2 Replies View RelatedIn A1 Downwards i have a list of dates for an entire year
I have highlighted these cells and wish to conditional format them.
I want every 3rd date from a specific date say in cell b1 to be highlighted up and down within the selected area. For example
____A _________________B
1 1/1/08 ____________ 5/1/08
2 2/1/08
3 3/1/08
4 4/1/08
5 5/1/08
6 6/1/08
7 7/1/08
8 8/1/08
9 9/1/08
Is there also a conditional format formula that i can use to highlight b1's day every month.
in this example highlight the 5th of every month again going up and down within the selected dates.
Im trying to get conditional formatting to look at a completion date in cell A5 and getting cell D5 to change colour depending on the length of time to cell A5. What I would like is for D5 to change to red if there is 4 months or less to the completion date in A5. D5 to change to orange if there is still 4 - 8 months from the completion date A5. D5 to change to green if there is still 8 - 12 months from the completion date A5. Hopefully the formula will also work if cell D6 is looking at completion date in A6 with the same traffic light system and so on.
View 3 Replies View Relateddate conditional formating issue
Valid to DatesConditional Formating
30.10.2013RED
2.01.2014RED
30.08.2014YELLOW
30.09.2013no formattingExpired
31.12.9999no formatting
The dates are in different format with dots in between as shown above Also with respect to date which has already expired, no formatting needed but i need a formula in the next column stating "Expired"
If valid to date is within 90 days from today- RED
If valid to date is within 365 days from today- YELLOW
I have a sheet where K5:K26 contains names of months. I would like to conditionally format that column so that any month that is less than TODAY appears red. So, if that column has a cell value of February, the cell would appear red since we are already in March. I would guess this involves a formula using TODAY, but I can't figure it out myself.
I know how to apply conditional formatting; I just lack the proper formula.
I have a list of dates that I want to flag as red if the date passes and the task is not complete.
So I would have 2 columns
Estimated completion date & Completion date.
If the completion date cell is null AND the Estimated completion date has passed then the Estimated completion date turns red (or a red block appears in a new column if that is easier!).
I have a large spreadsheet with dates. I want blank cells to be conditionally formatted to turn red and cells with dates older than 1 year to turn yellow. I have cell C1 set up as the current date (Today()) and the others reference the other cells with dates.
View 6 Replies View RelatedColumn C has dates in... and I have formatting that turns the cells in Column G Red if the date in Column C is 14 days or more old.
my problem is that if a cell in Column C does not have a date in it, the corresponding cell in Column G still gets formatted and I want it to not turn red if there is no date in Column C.
I am trying to use conditional formatting and its not working for me.
If I put the date 1 Jan 2014 in the cell of E2 I need a conditional format that will turn the cell yellow when it is two month from being a year old, so on 1 Nov 2014 it should turn yellow. Then I need it to turn orange when it is one month away from being a year old, so Dec 1 2014 it turns orange. And then when it is over a year old it turns red, So 1 Jan 2015 the cell turns red.
The formatting I have now turns them yellow, orange, and red but at the wrong times, not sure how to fix that.
We need to track the batteries if it is expired or not.Expiration is 3 months. once you received the battery, you need to put the date in the sheet (A1) and then the expiration will be automatically calculated in B1. another also, in B1, will change its color according to how may days left before the expiration date.
black color if the expiration is < 15 days
red color if the expiration is 45 days
white color if B1 is blank
I want to be able to format a whole row with a particluar text colour when a date is entered into column A. I have managed this no problems using conditional formatting. The problem I am having is that I need to change that row's colour again if both colomn A's date is complete and a date is also entered into colomn B.
I have tried conditional formatting without success but I have the niggling feeling that I have read this it is not possible to use this method.
I'm having trouble putting the correct conditional formatting on a column of dates.
Basically, in column 'A' I have a list of appointment dates. Column 'B' contains breach dates for a 10week target. And Column 'C' contains breach dates for an 18 week target. Looking something like this:......
I need to format column 'A' so that the cells will turn Amber if the appointment was after the 10 wk breach date and turn Red if it was after the 18 wk breach date.
I'm desperately trying to work out conditional formatting for different dates.
I have =TODAY() in F3
In the remainder of column F (from F19 to F625) I have either blank cells or varying dates (dating back to 2011 and dates going forward into 2014).
I need to Red/Amber/Green the following:
Green = any dates which are 45 days or more in the future from today's date
Amber = any dates which are between +1 and +45 days in the future from today's date
Red = any dates which are today's date or a date in the past
I can get one or two of the criteria working i.e. Green and Red, but not the Amber. Is one overriding or ignoring another?
I have a training sheet where the training could expire in either 1 2 or 3 years, i am trying to get the conditional formatting for each to change color when the training is due to expire in 6 months 3 months and 1 month also to show red when expired
View 14 Replies View RelatedI am currently setting up a project timeline, and at each stage i need the cell to change color depending on input. so for instance stage one happen -50 days. i work the date out need for completing and then the actual date is then put in the cell below which i want to go either
Green because it was done early or on time Amber if it has been done but was late Red if has not been done and the current date is now past the date of completion Stay Blank if it is Blank and their is still time to do it
for the green i am using a A2 <=A1
Amber i am using A2 > A1
for the red i'm using =And(A2<Today(),A1="")
the red one conditioning does not seem to be working and if their is nothing in the cell an its within the time its going green !
