Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Conditional Formatting (Red Is Older Than Today)

C2 Cell formatted for Date

Currently is shows red if its older than today showing something has expired using: Cell Value is less than =TODAY() and shows Green if it still OK using greater to or equal to =TODAY()

But i want:

Red is older than today
Amber is today or within 7 days
Green if date is over 7 days

Ive tried using 'between' but keep getting white cells.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Conditional Formatting (highlight The Cells Red )
i am trying to highlight the cells red in the b column if a3=d3 (if names match) and if b3<e3 (only if name is "a" should e3 be used to compare with b3). hope this makes sense. i've attached a sample worksheet.

i've tried =if(b3<e3, (vlookup(a3,d3:e33,1,false),true)...but does not work correctly. i need the average weight to remain constant according to the name when comparing, but if i drag the formula down to other cells, the average weight does not correspond to the name.

View Replies!   View Related
Use Conditional Formatting To Turn Cell Red If Any Cells Above Are Blank
I have a formula in A56 that adds cells A1:A54. I want to use conditional formatting to highlight A56 if any of the cells in A1:A54 are blank. I am unsure how to do this.

View Replies!   View Related
Conditional Formatting (column H Changes To Yes I Want The Background Of The Whole Row To Change To Red)
I have a spreadsheet where column H can be either yes or no. When column H changes to Yes I want the background of the whole row to change to Red.

e.g. Cell H4 contains the word no. I then change this so that it says "yes". I want the background of row 4 to go red. so A4, b4, c4, d4, e4, h4 all go red.

With the type of conditional formatting i normally do i can only get cell H4 to go Red by putting cell value = yes as the condition. I cant for the life of me think of a formula or way round of doing this.

View Replies!   View Related
Conditional Formatting - Objects (Red Arrow Points Down To Signify Negative Change)
I have two arrows:
- Red Arrow points down to signify negative change
- Green Arrow points up to signify positive change

These arrows look exactly like the Excel 2007 conditional formatting arrows you would apply to a cell - the only difference is that I have inserted them as shapes so I can float them over a graph.

GOAL: Corresponding with the graph, if a cell shows a (+) change, then I display green arrow and hide red arrow. Vice versa for a (-) change.

View Replies!   View Related
Conditional Formatting (start And End Times Of The Shift To Turn Black Or Red)
I want the start and end times of the shift to turn black or red depending on whether the adjacent cell says "off" of "Hol" respectively. I have this working except for when I actually enter smething into these cell ie a shift, the cell turns black.

View Replies!   View Related
Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells
Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??

View Replies!   View Related
Conditional Formatting (the Cell In 'Work' Is Coloured Red, If Not Then It Is Coloured Green)
I have a workbook (see attached) that has 2 sheets, Work and Holiday. I want to be able to enter a persons initials into the wrksheet 'Work' for each day of the week.

If that person is listed on the holiday sheet for that day as 'A' then the cell in 'Work' is coloured Red, if not then it is coloured Green. I have tried to get this to work using a defined list and various IF statements but all to no avail.

View Replies!   View Related
Conditional Formatting "1"=Black Or "Z"=Red In 1 Condition
I'm trying to create a formula in 1 conditional format rule. The goal is to check if al cel contains "1" or "Z". If the value is "1" the cell must turn black and if it is "Z" it turns red.
If this is not possible, I'd like to only turn it black.

I am not able to use vba and have only 1 conditional formatting rule left in my excel 2003 sheet.

View Replies!   View Related
Conditional Formatting: FIND Function? (get Cell A2 To Fill Red When Cell A2="Public" And A1 Contains "(P)")
Cell A1: International Studies Academy (P) 9-12
Cell A2: Public

Is there a way that I can get Cell A2 to fill red when cell A2="Public" and A1 contains "(P)"?. I can't seem to get a formula to work yet.

View Replies!   View Related
Conditional Format Where If C3 Text=red Then A3 Text=red
How do i do a conditional format where if c3 text=red then a3 text=red

View Replies!   View Related
Formatting Keyboard Commands (negatives In Red With Parenthesis)
I use keyboard commands whenever possible. They are much quicker than the mouse.

For years I have been using [Ctrl]+[Shift]+[1] to format cells as numeric with two decimal places, 1000 separator(,) and showing negative numbers in black with a minus sign in front.

I like to see negatives in red with parenthesis.

View Replies!   View Related
Using TODAY() In Conditonal Formatting
I have a spreadsheet where milestone dates are entered into cells.

I need these cells to highlight if the date in them is "today" minus 5 or "today" plus 5.

(plus or minus 5 days with respect to todays date)

Would someone happen to know how to specify this in the conditional formatting dialog box? I prefer not to use VBA...not familiar enough with it.

View Replies!   View Related
Condtional Formatting; Highlights My Data Range In A Row Red
I have a spreadsheet with conditional formatting that highlights my data range in a row red when their is no data entered in cell (E2) with this formula =COUNTIF($E2,"")>0 Now my question is...How can I make the highlighting go away if I enter information into cell (C2)? I added another condtion that said =COUNTIF($C2,"")

View Replies!   View Related
Conditonal Formatting (cell Automattically Turn Red Once The Date =NOW() Is The 15th Sept)
I have worked out the conditional formatting but i have a question. The cell has a date which is input manually - no formulas or anything
it reads 14/09/09. however can i have the cell automattically turn red once the date =NOW() is the 15th sept??

View Replies!   View Related
Autosave To Older Version
Please review the following and advise the one Best Connection needs to save in Excel 5.0/95 format without prompting me about incompatability issues

Sub Tomo() ....

View Replies!   View Related
Conditional Formating & "IF": Format To Red BOLD In Column
i m trying to do the following (with excel 07). if A2 is blank AND the date in A1 is today's date then format to Red BOLD in A1. also trying to have. if A2 is blank AND the date in A1 is today's date MINUS 1 day(so tomorow) then format to Pink in A1.

View Replies!   View Related
IF Statement Using Formatting Criteria (NOT Conditional Formatting)
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:

if(A1=blue background,"Yes","No")


if(A1=red text,"Yes","No")

etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.

View Replies!   View Related
Count Number Of Dates Older Than 30 Days
I need to figure out how to tell Excel to count the number of invoices on a worksheet older than 30 days and display that number in my Summary worksheet. Here's a quick example:

Worksheet '1234' has 10 invoices, 6 of which are more than 30 days old (invoice dates are in column B). Worksheet 'Summary' needs to display the total number of invoices and the number older than 30 days.

View Replies!   View Related
Automatically Convert Older XLS Versions To Newer
I have a dir with over 150.000 files in it, especially pdf, doc and xls. Now I'm looking for a script or program that will automatically search the entire directory and look for old xls files, open them and save them in a new(er) version. I'm working with a software that needs to use these XLS files in my directory but it can't use the old xls-versions. It's an impossible job to search by hand for the old versions and put them in a new(er) version.

View Replies!   View Related
Looping & Conditional Format: Finds A "J" It Will Apply Conditional Formatting To A Row Of 4 Cells Directly Adjacent?
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.

View Replies!   View Related
Conditional Format The List So That It Shows The Lowest Percentage In 'green' And The Highest In 'red'
I have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.

View Replies!   View Related
Conditional Formatting More Than Once
I have two spreadsheet "book1" with data in it, "book2" that takes all the information from "book1" the cells are linked in "book2" from "book1" so when someone updates "book1" the information is viewable in "book2"

In "book2" where I am getting stuck is as follows,

Column A contains data "break time" that is formatted in HH:MM:SS column B contains a "total duration" in HH:MM:SS. what I need to do is if column A data is = to or more than 4% of column B then I want to turn the cell in column A "red" if it is less then turn it "green". I have tried conditional formatting and it appears to work the once only, I dont no if this is because its 2003 and not 2007??

What I need is to repeat the conditional format over and over again forever is there any code for this I can use?

View Replies!   View Related
If/then/else Or Conditional Formatting?
I'm wanting to create a formula in column B that will return a value of 10 if column A is any value between 70% -100%, will return a value of 5 if column A is any value between 50% - 69%, and will return a value of 1 if column A is any value between 1% - 49%. Can you help me?

View Replies!   View Related
Conditional Formatting - More Than 3
In a column, there are 4 option for each cell -

These are filled in by a formula

I would like to make them 4 different colours but excel only allows 3 options

View Replies!   View Related
If Then Or Conditional Formatting??
I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?

View Replies!   View Related
Conditional Formatting
I don’t know if it is possible in Excel 2000 but I have a simple IF formula in a cell that gives "" on certain conditions.The problem I have is that in another cell I apply conditional formatting – green >100%, red<100% e.g.

Is it possible to have a third conditional format that gives a blank if the cell is ""?

At the moment it just turns out green as it doesn’t seem to recognise the cell as actually been blank as it has a formula within it.I have tried ISBLANK etc.

View Replies!   View Related
Conditional Formatting > 3
Conditional Formatting > 3

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim cr As Date
cr = Today()
If Not Intersect(Target, Range("C5:M44")) Is Nothing Then
Select Case Target
Case "X"
icolor = 10
Case "N/A"
icolor = 2
Case cr.Value >= Today()
icolor = 6
Case cr.Value <= Today()
icolor = 3
Case "DATE?"
icolor = 8
Case Else
icolor = 2
End Select
Target.Interior.ColorIndex = icolor
End If

End Sub

View Replies!   View Related
Conditional Formatting Macro
I am looking to create a macro that conditionally formats a cell depening on what words it contains.

if the cell has the word "level 1" in it = pink backround
"level 2" = red backround
"level 3" = Orange backround
"level 4" = Green Backround
"level 5" = Blue back round

i would like this to run on the active cell.

View Replies!   View Related
Conditional Formatting With IF/Then Statements
I need some help with applying some conditional formatting to cells that are populated with logic. I attached a portion of the sheet I am working on.

The cells in rows 41 and 42 will always be there so I am ok with them. The data in cells C43:I43 is loaded if a certain feature is selected on a separate sheet. If it is NOT selected, I need the values to not show up, (this part is already done with the formula in the cells) and i also need the conditional formatting to make all the cells white or blank. The same is true for the data in cells C46:I46

View Replies!   View Related
Conditional Formatting With 6 Conditions?
I'm using Excel 2000 which has a limit of three conditions, I have 6. If you could just get me started, I still don't understand VBA enough to do this.

In cell A1, the color is set to red. I want A1 to turn green when all 6 conditions are met:

1. Cell B1 has a valid value from its pull down list, no other value, and not empty.
2. Cell B2 has an integer, no decimal places allowed, and not empty.
3. Cell B3 has a valid value from its pull down list, no other value, and not empty
4. Cell B4 is not empty.
5. Cell B5 has a three decimal place number greater than zero.
6. Cell B6 has a single letter from A-Z only.

View Replies!   View Related
Conditional Formatting - Borders
i am trying to do the following but having trouble getting my head around it!

if a2=0 then b2:b13=border

View Replies!   View Related
Copying Conditional Formatting
Cell (A1) is formatted differently from the other cells of the same column. I added to it a conditional formatting (based on a formula) which I want to copy [alone] to the rest of the cells, without getting the other different formatting of cell (A1) copyed as well.

View Replies!   View Related
Formula Using Conditional Formatting
1. I have a range of random numbers within a column, i.e. A1:A9
2. A specific number will appear in a cell at the end of that column, i.e. A11
3. I need to see via conditional formatting which of the above cells add up to that number.

I am aware that given the numbers in A1:A9, it is possible to have more than one combination of numbers that add up to A11. I would need to see those combinations as well identified using Conditional formatting.

View Replies!   View Related
Conditional Formatting For Column D
I want a to include conditional formatting for Column D in my spreadsheet to highlight cells that have information in them but that are less than 17 digits. (The cell will be filled with alpha & numeric data)

How do I do this?

View Replies!   View Related
Extreme Conditional Formatting
I've exhausted searching the forum for help on this - Any input is appreciated
Using Excel 2007 , Range A1..M50

I'd like to color entire rows 1 through 50 based on what text label is inserted into any cell in Col A1..A50, Upto 10 labels possible ie. Ten colors needed.


Cell A1 = "Dog", color Red range A1..M1
Cell A1 = "Cat", color Blue range A1..M1
Cell A1 = "Bird", color Yellow range A1..M1
Cell A5 = "Dog", color Red range A5..M5
Cell A5 = "Cat", color Blue range A5..M5 etc

View Replies!   View Related
Conditional Formatting Limit
I have an excel-sheet with two worksheets one called "sheet1", this is where all data is stored, and "sheet2", where data from sheet1 can be evoked.

I'm now looking for some code that get arounds the 3 conditions limit of conditional formatting. Most of the codes on internet, just do this but only when entered manually on the selected sheet. In my case all data is entered on sheet1 and are displayed by some formulas on sheet2. It is on sheet 2 where I would like the color change to happen.

I found a piece of code that was said to do just that, but when testing it appears not to. It only updates the cell color when the concerning cell is clicke twice.

View Replies!   View Related
Conditional Formatting With More Than Three Values
Im using access 2003 and the conditional formatting is only limited to 3. But i wanted 5. I was told it can be done using VBA. But I've got no programming experience whatsover.

Basically this is what i want, but i dont know how to translate this VB

IF Cell Value = >1 then make the value "Bold", "Bordered", and "Fill" with Orange colour.
IF Cell Value = "H" then make the text "Bold", Bordered, and "Fill" with Green colour.
IF Cell Value = "S" then make the text "Bold", Bordered, and "Fill" with Yellow colour
IF Cell Value = "A" then make the text "Bold", Bordered, and "Fill" with Blue colour
IF Cell Value = "U" then make the text "Bold", Bordered, and "Fill" with Red Colour

View Replies!   View Related
Conditional Formatting - Months
I'm trying to conditionally format 2 cells in a row based on when an ARP is due.

The data I'm working with looks a bit like: ...

View Replies!   View Related
Can We Do OR Function In Conditional Formatting?
Is it possible to use OR function in Conditional Formatting? I want to do the following.

If A1 = 1, A2 cell is filled in Red
If A1 = 2, A2 cell is filled in Red
If A1 = 3, A2 cell is filled in Green,
If A1 = 4, A2 cell is filled in Green,

I only managed to do the above for 3 conditions as Conditional Formatting only allows me to no more than 3.

View Replies!   View Related
Conditional Formatting Using A Macro ...
I am trying to work with conditional formatting using a macro.

How it works is I select the range I want to format manually then I run this macro.

It doesn't work

When I check the conditional format the formula is wrong. Instead of referencing the correct cell in columns E & F is is referencing E65282 & F65282

What do I need to change? ...

View Replies!   View Related
Conditional Formatting/Duplicates
My background is many years of Access VBA. From time to time, I'm called upon to work in Excel. I am not that familiar with the library.

I'd like to know if it is possible to do the following:

Examine Column 1 and, for each like value, examine Columns 2 and 3 for duplicate values. Highlight or conditionally format the duplicates in Columns 2 and 3 (again, for each like value in Column 1).

Being that I've coded Access applications for quite some time, I am sure it can be done...I just don't yet know exactly how. I've searched the archive for and found some examples which deal with duplicate data, but have found none yet which quite fit my scenario.

I've found sample code which enforces rules and conditionally formats cells when new data is added. It works fine and I understand it. I'm hoping the kind soul who might help me with the above can explain how I might do both things on the code page.

I assume the Worksheet_Change Event would contain the "enforcement" code.

View Replies!   View Related
Conditional Formatting Using Pictures
I am using MS Excel XP and with the basic package. I need to find out is there an easy way to assign a picture such as a smiley face to appear within in a cell when it meets a specific formula condition. Similiar to utilising conditional formatting and shading cells or text; however, a picture would appear instead.

If the cell met another condition that was less favourable a different picture would appear such as and if an even worse condition was met based on the formula another picture such as would appear.

The picture would constantly be able to be changed based on the result of the formula.

* Smilely faces only utilised as an example.

View Replies!   View Related
More Than 3 Conditions For Conditional Formatting
I have tried it, and it isn't changing the cells when I test the macro. I'm obviously doing something wrong, but can't figure out what....

View Replies!   View Related
Conditional Formatting For +6 Months?
Ok, I'm going to use row 2 as an example.

If collumn AD row 2 = NI

Then I want collumn AE row 2 = Todays date + 6months (EG if todays date was the 18/08/08 then it would read 18/02/09)

And I want that for EVERY row except row 1

Is this possible with Conditional formatting, or do I have to fetch out the old VB code again?

View Replies!   View Related
Copy .. Conditional Formatting
Is there an easy way to copy conditional formatting? I need to copy the same scenario so that it adjusts for each row that it's on (the way formulas do).

For instance, I need to copy cells I4:L4 all the way to rows I84:L84. And I want the rows to adjust depending on what row they're on, like formulas seem to (as apposed to referencing the original cells).

Does that make sense?

View Replies!   View Related
Conditional Formatting With 4 Conditions
I am using a drop down list in excel and want to conditional format the cells based on what is selected from the list. I can't use conditional formatting as I have more than 3 items in the list.

I am guessing VBA can help but am a complete novice so could use some handholding on what to do.

Idea is that if someone select "Good" from dropdown, cell becomes green, "Stable" is amber and so on.

View Replies!   View Related
Conditional Formatting More Then 3 Conditions
Is it possible to apply confitional formatting on 6 conditions ...

View Replies!   View Related
Conditional Formatting, Any Way To Get More Than 3 Formats?
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 Replies!   View Related
Conditional Formatting As A New List
Is there any way that I can use conditional formatting and instead of highlighting top 10% to have them displayed as a new list.

If that's not possible how can I have highlighted conditional formatting answers pulled to a new list for example: Top 10% list.

View Replies!   View Related
Conditional Formatting With INDIRECT And AND
I'm trying to conditionally format a cell based on the cells around it. There might be a better way to do this but this is what I have done. I'll just show some trials of formatting conditions I've done.

View Replies!   View Related
Copyright © 2005-08, All rights reserved