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:
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
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
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
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
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
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 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
Conditional Formatting To Get Rid Of #DIV/0!
I have my formulas in place and they are working. The reason why some are coming up with #DIV/0 is because I do not have the data to complete the calculation.

Is there a way to make the #DIV/0 error go away until I imput the data to complete the calculation? I was thinking there might be a way to do it with conditional formatting.

View Replies!   View Related
Conditional Formatting Through VBA
Sub Macro1()
With Range("A1")
.FormatConditions.Add Type:=xlExpression, Formula1:="=A1=B1"
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, Formula1:="=A1B1"
.FormatConditions(2).Interior.ColorIndex = 50
.AutoFill Destination:=Range("A1:A29"), Type:=xlFillDefault
End With
End Sub

If my active cell is NOT A1, the formula entered into conditional formatting is off. The range references get all messed up. For example, if my active cell is Say D5

Condition 1 is
Condition 2 is

But if I run the code with A1 as my active cell, it gets entered correctly as
Condition 1
Condition 2

View Replies!   View Related
Conditional Formatting As $D8 And The RC4 As $C8 In VBA
Must admit the R1C1 still confuses me, but somehow I have this working. Currently the RC4 displays in conditional format as $D8 and the RC4 as $C8, but I'm confused at how it knows to start at R8? Is there any way to make the formulas read ="=$D8=""No""" instead?

View Replies!   View Related
Set Conditional Formatting Via VBA
I would like to add conditional formatting for the whole row based on whether the value of cells in column A is "CON".

For Each c In r
If c.Value = "CON" Then
c.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:=c.Address = "OK"
c.EntireRow.FormatConditions(1).Interior.ColorIndex = 35
end if
next c
the "CON" is a formula and can later take the value "OK".

The conditional formatting should highlight the whole row as green when that happens.

View Replies!   View Related
Conditional Formatting With Vba...?
I seem to have a problem using conditional formatting with Vba in Excel 2003
When I run -

View Replies!   View Related
Conditional Formatting / VBA
I am not sure if this requires conditional formatting/vba function

If I enter something in cell A1 and leave blank in cell c1, in cell c1 it should print the message "Required Entry, Please Fill up". and this condition should be applied to entire column, where ever in column A there is some values and column c is empty this condition should apply.

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 A Row
I have used conditional formatting various ways to format an individual cell. I was wondering does anyone know if it is possible to conditionally format a row? Eaxmple: If D7=Manager, then format A7 through Z7. If so, how would I go about setting that up?

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 With Vba
I've written a macro that copies a range of cells (size varies) from Sheet1 to Sheet2, deletes the conditional formatting already there, and applies fresh conditional formatting to Sheet2 on the new range. But, the expression in the FormatConditions is way different than what I specified in the code. Here's my Conditional Formatting

Set mt1 = tData.Columns(1). Find(What:="", After:=tData.Range("A1"), LookIn:=xlValues)
tData.Range("A2:A" & mt1.Row - 1).FormatConditions.Delete
With tData.Range("A2:A3" & mt1.Row - 1).FormatConditions _
.Add(xlExpression, , "=A2=A1")
.Font.ColorIndex = 2
End With

When I look in cell A2, I see this for the expression: "=A65301=A65300". Any idea why it isn't starting with "=A2=A1" as I specified in the code?

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
Conditional Formatting / Add Duplicates
I need to create a formula that will look at a p/n find its match and add the value from all matching p/n together. After this is done, all of the line items that matched I need them to turn red. But I do not want the first p/n to turn red. It is better explained in my example.

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
Conditional Formatting To Hide A Row
In Excel 2003, I'm trying to auto-hide rows when the value in any cell of a certain column is "Closed" This can't be done via conditional formatting. Any other ideas (VBA code)?

I found below code excerpt but it did not work for me. Maybe it's not specifying what cells it should look in... or I'm not doing something correctly.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Value = "Closed" Then
Rows(Target.Row).RowHeight = 0
End If

End Sub

View Replies!   View Related
Conditional Formatting Of 6 Variables Using VBA
I have a spreadsheet with some validation in, and I need the row to be highlighted depending on the criteria selected in the validation. I know Excel is limited to 3, so any more than that has to be done using VBA but I'm not 100% sure on the code.

The validation I have is in Column V and contains the following:

Received and being processed
Incorrect/Incomplete/Invalid information provided
With Disclosure Scotland
Authosied without paperwork

With the first one, I want this to be classed as the default and so no formatting applied, but for the rest I need the rows to be highlighted to differentiate at a glance.

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
How To Add More Conditional Formatting Conditions Than 3?
Why is there a hard limitation like this? Is there a way to go around it? By having a different format for the cells outside the 3 conditions I can get 4 different formats altogether, but that's not really that much.

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
Vlookups And Conditional Formatting.
I would like some conditional formatting to happen when the vlookup is true AND false.

The lookup is looking for a number, if the number is there, i want it to colour the background RED. When it is not there (the vlookup says #N/A) i want to have it coloured YELLOW.

View Replies!   View Related
Conditional Formatting On Due Date
Conditional Formatting for Due Dates. I have A1 which is due date and I want A1 to be formatted with red text if it is 2 days before the due date.

View Replies!   View Related
Conditional Formatting On Certain Text
I am automating an invoice template for an excel novice, thus I want to conditionally format certain cells dependant on what text is in them.

I want to do this in VB but I don't know how!

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