I have applied conditional formatting to a column of cells to highlight the cell red when the date contained in it is > 180 days. The formula applied to all of the cells is =TODAY()-180. Works perfect, however, there are cells with no values (dates) entered yet and they are also highlighted.

Ideally I'd like the cells with no date in them to remain white until a date is entered and then apply the conditions after a date is entered.

Thanks in advance for any help.

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link __ http://downloadreimage.com/download.php__. (This link will automatically start a download of Reimage that you can save to your computer.)

hi everyone.

basically in my spread sheet i have created a sum that is as follows: "=K3*(IF(B3<I3,B3,I3))" (it is used in other cells aswell but this will do as an example). basically i want to set up conditional formatting so that if the value in B3 is used the cell turns green, if it uses I3 the cell turns red. however i cant think of a formula that lets me do that.

a reply asap would be great as its due for tomorrow

thanks again

13 more replies

I am using Excel 2003 in which I am maintaining a database of Material Safety Data Sheets (MSDS) each MSDS expires 3 years after the issue date. I need know how to to change the cell colour of each cell when the expiry date is reached.

If all you want to do is color the cell or cells on or after your date has reached 3 yearstry this:I'm using 2007, so you may need to adjust these instructions for 2003:With your Issue Date in cell A1Select cell or range of cells On the Task BarSelect Format - Conditional FormattingOn the first dropdown, choose Formula IsFor the formula, enter:=TODAY()>=DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))Click the Format button.Select a pretty font colour .Click OK Click OKWhat the formula does is simply adds three years to your issue date in cell A1 and compares it to todays date.See how that works.MIKEhttp://www.skeptic.com/

2 more repliesIf I wanted to make a conditional formatting rule that would select all cells in a column [I2-I65] with a formula generated value less than 0.9 and greater than 1.1, ignoring all empty cells, what formula would I use?

Thanks.

Hi Guys - I have two columns with sales data - January and February. I need to determine if February sales data saw a 10% or higher increase. Sales meeting this criteria would be highlighted in Red text. What's the easiest way to set this up?

This may help you get started: https://support.office.com/en-au/art...b-f1951ff89d7f

2 more repliesI am using conditional formatting to match a value in cell A1 to a list of values. Is there a way to keep the formatted color of the matched cell in the list after I enter another value in A1? i.e. I have a bunch of numbers that I want to verify and show that they all matched the values in the list. It's like a cumulative record, match the entry - turn a color and stay that color.

It appears that you changed the requirements given in your original post:"Is there a way to keep the formatted color of the matched cell in the list after I enter another value in A1?""It's like a cumulative record, match the entry - turn a color and stay that color."Now you are saying that you want a one color for the current match but then have that same cell turn a different color once a new valued is entered in A1? If that's the case, my code won't work.This code will highlight the entire row, but all in the same color as the other previously matched cells. Do you have the "current match CF" portion already worked out or does that somehow need to be added to the code?Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then

With Columns("B:C")

Set c = .Find(Target)

If Not c Is Nothing Then

Range(c.Address).EntireRow.Interior.ColorIndex = 6

Else

MsgBox "Value Not Found"

End If

End With

End If

End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Let's say I have a simple spreadsheet with 5 columns of data, and 10 rows. Column E has the totals of columns A thru D. For each cell in the total column (E1, E2, ...), I'd like to "hide" that row if the total is 0. Does anyone know how to do this?

Can you provide sample code to do it in Visual Basic?

Thanks in advance!

Jack

Hello,

I have a bunch of columns that I'm trying to use conditional formatting on. I want to turn the min value blue and the max value red. That part is pretty simple and working fine. However, the problem I'm running into is I'm using the accounting format in the cells and when a value is null or zero, it's turning blue. I want to avoid that and have it ignore all empty or 0 fields. The cells are linked to another master sheet. Can anyone help me out so I can have the conditional formatting only looking at the cells with actual numbers greater than zero in it?

Thanks in advance.

Is it possible to conditionally format a cell depending on a value or string in another cell and the to be formatted cell contains a valuee.g. cell A1 = 2cell B2="xxx"cell A2 is to be formatted to colour -green

Yes. In the dialog box for Conditional Formatting there is a drop-down box. Select Formula Is then enter a formula like:=B2="xxx"

2 more repliesHi - I have a spreadsheet with data in columns A - J and thousands of rows. I want to highlight a particular row if I put the word 'no' into any cell in column B, in any row.

I don't want to just fill the individual cell and I only want the highlight to go from A to J. I've tried various examples of conditional formatting but I'm not getting what I want.

Could someone point me in the right direction, please? It's Excel 2007.

"I want to highlight a particular row if I put the word 'no' into any cell in column B, in any row"

Always the same row, no matter in which row the 'no' is entered?

Hello! I'm having a bit of trouble with a formula.

B3 is a text value (like 0903, 0904... the trailing zero is why it's stored as text) that the user enters. D3 needs to lookup that value in another worksheet. I've named that range "DEL", it's just two columns with the result coming from the second column.

Here's the tricky part:

If there is nothing entered in B3, nothing should appear in D3.

If there is something entered that does not appear in "DEL", D3 needs to say "Unknown", be filled with red, and have a message box come up with "Warning, Could not find the delegate".

If the value CAN be found, then the cell needs to be filled with a tan colour.

How on earth do I do this? I've been using VLOOKUP and IF statements, but I can't seem to get around the error messages and making them come up as "Unknown".

And yes, I need it to do exactly this - no changes. Thanks!

Anyone know why I can't apply conditional formatting to a cell when the content of a cell is a formula? More importantly, how do I get around it? (Example - cell contains a formula to display "Yes" when other conditions exist. I want another cell to reference the "Yes" cell and display an icon based upon the reference cell being a "yes" or a "no")

you should be able to

can you provide a sample spreadsheet and an exact description of what you are trying to achieve

what ICON are you trying to display ?

I may be able to solve this issue with conditional formatting, but I'm not sure exactly how, and I'm not sure it is the best method. I have cells with data that all rely on formulas. I've formatted the pages so that you don't see the zero results. I have other cells that will always have data but sometimes I want it visible and othertimes not. I can manually change the font color to white, but I have over 100 tabs all with various data that needs to be changed back and forth. Basically if Row 10 has a value in cell AW I want all of the text in that row visible, but if the forumla result in cell AW10 is zero I want all of the text white. I hope this makes sense, I would like to have this apply to over 100 tabs so I would like to have a macro do this for me. If that is best - I'm really not sure. The range of cells is A10:BF55, the value the condition is based on is always AW. And of course to complicate things a little further I want this to apply to most, but not all of my tabs. There are about 6 tabs that I don't want formatted. And the names of the tabs aren't a series (some of them are, but not all). I'll try to attach an example later.

I don't remember if I asked this before, but I have a range of cells that I want to change color depending on whether the value of one of the cells in the range is "greater than or equal to" or "less than" the value of another cell in the same range.

I've figured out how to change the "Cell Value Is" dropdown to "Formula Is", and that works for the most part, but if I have a range of 10 cells, only the one in the lower right is completely correct. The other cells have their formulas changed to reflect the position changes within the range. ie. if I have cells A1:B5 selected, and have the formula set to: =B4>B3 (red) and =B4<B3 (green), then only cell B5 will contain these formulas. Cell A5 will show: =A4>A3 (red) and =A4<A3 (green), etc...

How can I make it so that ALL cells will indicate =B4>B3 (red) and =B4<B3 (green) ?? - without having to manually go back into each cell and "fix" it...

Use the $ sign before the rows and columns to make them "Absolute", i.e. =$B$4>$B$3

You can just make the Column OR Row Absolute, i.e. $B4 or B$4

hi guys,

In a simple spreadsheet I tried to set up conditional formatting, to fill a field if a condition is met.

for example, i maintain a spreadsheet encounting for our trailers on a daily basis (see attached file)

what i try to achieve is when i fill in a trailer number in the top half of the sheet, that the corresponding trailer number field at the bottom is filled (with a colour).

I didnt manage to figure out how to get it to work and hope that one of you clever people can help out!

regards,

Martin

I have a workbook with 3 sheets. Sheet "PS", column A contains a list of player names that stays static. Each week, each person in column A sends me a name from an available list. They are allowed to use each name only one time per year. The first name they send I put in column B, the second in column D, the next in column F, etc. Sheet "Data" has each player in a column. I have a formula (=PS!$B$4) etc. in each cell going down to pull the name from sheet "PS" corresponding to that week. I want to easily see duplicates, (which is against the games rules). I can use conditional formatting to change the font color and that does work. I have my Excel set to not show anything in 0 value cells. Because of the formulas, each cell below the entered names has a zero value which makes it a duplicate. I want to highlight the cells that contain a duplicate name. I just can't figure out the formula so I don't highlight all the cells with zeros as well. I'm very new to Excel so I apologize for the misuse of the proper terminology. I can certainly attach this workbook if it would help.

Thank you in advance for your help.

Hi There,

In Excel 2010 I am trying to is apply conditional formatting to a range of cells, the conditions for formatting I am trying to implement are

If the cell is not blank and if the length of the text in the cell is not 11 then format

It works when applied to a single cell using

=AND(IF(($I9)<>"",TRUE,FALSE),(IF(LEN($I9)<>11,TRUE,FALSE)))

but when I try to apply it to a range (see below) it does nothing

=AND(IF(($I5:$I4000)<>"",TRUE,FALSE),(IF(LEN($I5:$I4000)<>11,TRUE,FALSE)))

Any advise would be greatly appreciated

Thanks in advanced

Chris

I am trying to apply condtional formatting through VBA. Here is the code I am playing with at the moment.

Code:

Range("D2:D" & LastRow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR(D2<>0,D2<>""""),A2<>123,A2<>987,A2<>852,A2<>753,A2<>""User"")"

Range("D2:D" & LastRow).FormatConditions(1).Interior.PatternColorIndex = xlAutomatic

Range("D2:D" & LastRow).FormatConditions(1).Interior.ColorIndex = 36

Range("D2:D" & LastRow).FormatConditions(1).Interior.TintAndShade = 0

When I test the conditional formatting on the worksheet itself, all appears to be working. However, when I try to apply the conditional formatting through VBA it is not working at all.

Can someone please point me in the right direction? I believe I am missing something simple, but I have not been able to place my finger on what I am doing wrong.

Hi Guys,

I wonder if any of you can point me in the right direction.

I need to set up a conditional formatting. if the value in any of the cells in column C (row 3-73) is "Y" I would like the row in which a "Y" is found filled (from A-P

Thanks for reading

mad-martin

Basically im trying to change the fill color in cells based on their StDev. 1StDev=Yellow, 2StDev=Orange, 3StDev=Red, BUT, each column has a different StDev, so im forced to do this manually, but theres gotta be an easier way. I have close to 200 columns alltogether...

I tried recording a macro but can't seem to figure out how to get the conditional formatting formula code to move over to the next column...anyway, i don't want to make it anymore confusing so i'll stop talking, any help?

Hi, I have used a conditional format to change the colour of a cell (A30) depending on the value of another cell (B30), there are 6 possible values therefore I have created 6 rules for this cell (A30) I now want to copy the same conditional formatting to cell A31 but relating to B31, and so on. How do I do this. Many thanks.

if you have set the formula up in the conditional format - to be B30 and NOT $B$30

then you can copy

paste special

format

and copy to A31

Hi - I want to see if there is a way to write a formula within conditional formatting, for when the the dependent variable starts with certain letters. For example, right now, I have a formula that looks like this: =OR($D1="6Q314068",$D1="JOY14068",$D1="6Q376035") I have multiple variables that start with "6Q3" and more than one (though not demonstrated here) that start with "JOY." Is therer a way to write the formula so that I only have to write the three characters that are really the dependent variable, regardless of what the rest of that cell contains? I.e. if the cell contains 6Q314068, can I make the conditional formatting conditional on the cell containing 6Q3, but not only 6Q3? Does this make sense? If I can, hwo would I write this (I am envisioning "6Q3*" or something like that....) Thanks so much!

I am trying to apply Conditional Formatting to cells D3:D400 based on two criteria, and I just can't seem to get the formula right.

I want cells in D3:D400 to highlight if C3:C400 contains any value AND D3:D400 contains no value.

The function I am using to attempt this is =IFBLANK(D3:D400)

I am using Excel 2000.

Can this be done?

Hi,

Usually I have no problem with conditional formatting but I don't understand why the following won't work.

I'm using Excel 2003, where I have two date columns (in columns A&B) and I want to format Date 1 according to these rules

Date 1 exists, Date 1 is > 3 days ago, Date 2 does not exist = RED

Date 1 exists, Date 1 is <= 3 days ago, Date 2 does not exist = ORANGE

I used the following conditions

=AND(A2>0,TODAY()-A2>3,B2=0) .... RED

=AND(A2>0,B2=0) ... ORANGE

When I first pasted the format all the way down the column it seemed to work just fine. But then, if I enter or delete a date in column B, the formatting in column A does not update automatically. However, if I double-click the date in column A, then it does work. But I thought it should happen automatically. Users will be opening this sheet daily and I want them to be alerted to these cases.

I'm attaching a version with just those two columns, in case it helps. I'd be really grateful if someone could help me understand where I'm going wrong.

Pat

Hi,

I have searched all sorts of solutions but i cant seem to find the formula to get this to work.

I have a date in Column A4. In column R4 i then have 'due date'. I would like this to be one week before the date in A4.

If this is not possible then it is not the end of the world as i will just duplicate the date in A4 and use extra conditional formating.

What i want is the date in R4 to conditional format based on how many days away we are from that date eg. 14 days from date in R4 to be yellow and 7 days to be red.

Really appreciate any and all help

Irving

Hey Guys,

I am currently working with Excel 2007, but need this spreadsheet to work on older versions of Excel also.

I have a training tracker spreadsheet, containing details of what training has been completed by an employee and when.. some of this training is one off (machinery etc), but some of it is required to be repeated on a timescaled basis (6 months, annually, etc.).

The fields currently contain the date the employee had their training signed off.. how can I make conditional formatting work so that say, after 6 months, the field colour turns red to make it stand out...??

Thanks in advance... let me know if a copy of the speadsheet will help, I can remove all personal details and give you a copy to play with.

Cheers

Hiya

If you set the selection with the details as in the attached picture it should work.Please let me know if it doesnt and if it does then please mark this thread as solved.

Hello;

I have a spreadsheet that is designed to keep track of "deals" that are written in a car dealership. What I want to do is create a conditional formatting scenario that allows me to change the entire row color by changing a drop down box that changes its status. For example, in the status column of the spreadsheet, there would be terms such as "delivered", "approved", "tanked" and so forth. Once the term is selected via drop down box, the entire line (row) would be change color. Yellow for approved, Red for tanked, etc.

I have setup the drop down box using validation and it works fine. Once I setup the conditional formatting, the first row works flawlessly. If I attempt to copy the formatting to the subsequent rows, the change in the first cell makes ALL of the rows the color of choice. I want to make changes to each of the rows based on the status of the one cell.

You'll notice in the sample spreadsheet that I have attached that the first row works with the status changes and the color changes, but it is not transferrable to subsequent rows.

I have tried several methods to make this work, to no avail. I have attached a copy of the file in zip format so that an expert can see the spreadsheet and see what I'm talking about. I DO NOT want have to make conditional formatting rules for every row every time. What is it that I'm missing here?

Any help would be appreciated. I have posted this question on other forums ... Read more

Hello,

I have a table that has some dates in it. They are formatted in a ddd-dd-mmm format, ie, "Sun-12-May". I am trying to conditionally format the cells so that the one that have "Sun" and "Sat" the text is in a different color. I thought I would use the "Format only cells that contain" rule with "Specific text", "Containing", "Sun" or "Sat", but that doesn't seem to be working. I have also tried separating them, one rule for Sun and one for Sat, but still no luck, what I am missing?

Thank you for your help,

Matt

I would choose is not a workday or you could use day of week

WEEKDAY(serial_number,return_type)

an use the return type 2

so that sat and sunday are 6 and 7

so you can then test for >5

IF(WEEKDAY(Cell,2) ) should work

i'll have a play

Yep that works

so in the conditional format

use

formula is

=(WEEKDAY(A2,2)>5)

i have attached the spreadsheet

Not sire on later version of excel - but on older version you needed to

goto

tools

addins

and make sure

analysis toolpack - was ticked for weekday to work - I think thats the case

I need to format a cell based on its value compared to two other cells. I am using "Formula is" option for Excel and using AND function to set the color to green.

=AND($AM$11>$AR$11,$AM$11>$AQ$11)

AM11 is indeed greater than the two cells, but the color is not being set to green. Not sure what am I missing here.

Again this is Excel 2003.

Thanks.

you have marked solved - is this the case ?

working for me

I have attached the file

The conditional format is in AJ4

Did you put the = sign in the formula?

Hi,

Is it possible to use "AND" or "OR" somehow in a conditional formatting expression? I would guess it is but I cannot work out the right syntax, or even which of these I need.

Let's say I have two columns A and B. They contain dates or they may be blank in any given row. They relate to dates that two parts of a process have been completed. I need to highlight a cell in column A, if

A is blank and B is NOT blank

OR

A contains a date after the date in B unless B is still blank.

Does that make sense? People may go back and input dates at any time, and I want to highlight that they have entered process 2 without having first entered process 1, or if they have accidentally entered a process 2 date earlier than process 1. (The same date is possible, though unusual.)

Grateful for any help.

Pat.

A is blank and B is NOT blankClick to expand...

change the first drop down to

Formula Is

then in the next box

=AND(A1="",B1<>"")

and set the format for a true condition

2nd part

A contains a date after the date in B unless B is still blank.Click to expand...

now click on the ADD button to get a 2nd condition

=AND(B3<>"",(A3>B3))

I have an imported table of information which I wanted to highlight certain cells which match specific criteria. I tried several methods to get them to highlight.

Problem: Although they will highlight, when I copy the conditional formatting, they don't update automatically. I can get them to update, but it takes double-clicking inside each cell to update and recognize the conditions.

I have tried formatting each cell again; number format, general format, etc. No luck.

I also tried format painting, format copy and pasting.

Is there some way to update the list and force the formats to be recognized?

Thanks,

Work Around Found

I Used the REPLACE function to replace all the cells with the identical information. Sounds redundant but it worked. The cells updated and recognized the conditional formatting.

Problem solved, for now!!

Howdy!

okay so I'm creating a new spreadsheet for work and have to format the cells in column C labelled No. Accidents. if the number of accidents is less than 5 i want it to be green and if its equal to 5 I want it to be yellow and more than 5 i want to be red.

That part I am fine with

BUT

it counts blank cells as 0

so I want it to be able to ignore these blank cells and leave them as white and not be affected by the conditional formatting colours

Please Help!

Cheers

I need to do a Conditional Formatting function using VLOOKUP to compare the value in one column to the value in another column. The first table shows what the table looks like with no formatting. The second table shows what I want the table to look like after I get the conditional formatting set.

I want the first column "red" value to highlight green if it's higher than the second column "red" value. And on down the list. I want the first column value to highlight green if it's lower than the second column value.

Red1White3Green 2Black5Blue3Red3Black4Blue1White5Green4

Red1White3Green 2Black5Blue3Red3Black4Blue1White5Green4

Hiya Guys,

My question:

I need to color a cell (merged) based on yes/no data in a column range. I already have a simple conditional for the "Complete" rows to turn green if "yes", but I need the supplier to turn green if ALL of the "Complete" rows are "yes" and there are no "no" (and ignore blanks).

My current seems to only apply to the first field in the range and I have no idea how to make it ignore blanks. Any help is appreciated!

Hi there Guys,

I need some help with Excel 2003. I cant post and example up as it is confidential information. But I want explain what I need the best I can.

I have a spreadsheet with 8 Columns (A to H). The columns I am most concerned about are Column E and Column H.

In column H, I have applied the following conditional formatting:

If Cell Value is equal to 3, the cell should be filled in red.

If Cell Value is equal to 4, the cell should be filled in orange.

If Cell Value is greater then or equal to 5, the cell should be filled in green.

In column E I have numbers and whatever I enter there is automatically added together and the total is displayed in a different cell.

I got all that right, but now I want to do this.

If the a cell is red (or 3) in Column H I want all the corresponding line numbers in Column E to be added together and displayed in a different cell. I want the same rule to apply a cell in orange (4) and a cell in green (5 or greater). Can this be done. If I get this to work then the rest will be easy.

Can anyone help please??

Hello,

I seem to be having a problem with conditional formatting in Microsoft Excel.

I have attached the file I am working on that I'm having the problem with.

I have a spreadsheet where I log my bills and payments (the figures included in the attachment are obviously fabricated). The spreadsheet is designed to tell me what payments I am meant to make and when I am meant to make them, all the while providing me with a 'live' update of what the original debt/bill total was, and how much of that debt/bill total I have remaining).

As you can see in the 'Owed/Budgeted Amount' row, there are cells with the '$0.00' value in them. This indicates that the debt/bill total amount is accounted for over the following payment fortnights. However, as some of these amounts change as time passes, I would like each of those cells to appear with a red background (like the cell appearing underneath the '3 Mobile - Mobile Phone' cell) when they equal a value that is over or under $0.00.

However, the conditional formatting I have that should control this does not appear to be working correctly. At the moment, as you can see with the attached file, cells 'B6' to 'J6' have a conditional formatting that will make the background colour of the cells appear in red if they fall below or above $0.00. However, as you can see, the '3 Mobile - Mobile Phone' category cell appears with a red background even though it is neither be... Read more

Hi Guys,

I am looking for some help with Conditional Formatting on a 2007 Excel ssheet.

I have the following info:

Column C = Due Dates. This contains various dates either side of Today()

Column G = Status. This contains a single entry from a pick list of possibles such as "Not Started", "In Progress", "Complete" etc.

I would like to flag the cells in column C if the due date is on or before Today AND if the Status entry does not = "Not Applicable" or "Complete". EG flag those lines where the task is overdue and has not been completed or discounted.

I can do the first part of the requirement using <=Today() but can't get it to factor in the ones with the Status I require.

I've tried a few fomulas but with no joy - examples of what I have tried are below:

="and($C:$C<=today();($G:$G≠""Success"")"

="and($C:$C<=today();not($G:$G=""Success"")"

and variations on this theme.

Any suggestions would be gratefully received.

cheers,

Su

thank you for trying to help... still learning Excel...

So I am trying to have a Named list of choices used as Drop down lists for cells in columns AK, AH and AJ. These choices are "Blank", "1" through "8" and "A".

Then, using conditional formatting via setting a formula to determine, if all three cells from these columns in a single row equal "1" through "8" (i.e. ="<=8"), then another cell in that row, D3 will be a specific color.

the Conditional formula I am using is =AND(AK3="<=8, AH3="<=8", AJ3="<=8"). This is applied to D3300 (for every row in the column.

This doesn't change the color in D3. If I change the conditional formatting to query for "A" instead, it does work.

It sounds like there is something fundamental that I'm not getting. Any suggestions? thank you!!

you do not want the " in the expression

=AND(AH3<=8,AJ3<=8,AK3<=8)

see attached

worked for cell

H, J, K

and

AH, AJ, AK

Hi,

I have set conditional formatting to colour a cell if a corresponding cell is populated.

This works fine.

When I try and copy the formatting to other cells it does not use relative referencing but continues

to referene the first cell.

I have tried selecting the different 'copy' options to no avail.

can anybody explain what to do, please?

please see attached spreadsheet. I am using excel 2007.

Thank you..

Where you have

=NOT(ISBLANK($G$8))

remove the $

so it reads

=NOT(ISBLANK(G8))

press apply, press ok

and now when you drag this down it will work for all adjascent cells

I'm new here (hence the first post) and I'm not very literate with Excel.

I apologize in advance if this has been asked and answered, but I searched a number of the posts that relate to conditional formatting and don't see anything.

I'm trying to automatically generate text when a cell doesn't meet a certain condition. In other words, I want "WRONG" to automatically generate in Cell C1 when Cells A1 and B1 aren't equal.

You can do this with a Formula in the cells of Column "c". the formula is

=IF(A1<>B1, "Wrong","")

You need to enter it in the first cell and then Drag/Copy it down.

G'day,

I have another question regarding conditional formatting in Microsoft Excel!

I have a list of cells which contain text specifying fortnight periods. For example, one cell has '19th June, 2008 - 3rd July, 2008', another cell has '3rd July, 2008 - 17th July, 2008', and so on.

I was hoping to include a formula so that when today's date is within the range of one of those fortnight periods, the cell containing the fortnight period would take on a particular format. For example, today is the 1st of July, 2008, so the cell containing the '19th June, 2008 - 3rd July, 2008' cell would take on a red background indicating that it is the current fortnight.

If anyone is able to guide me to a solution, I would greatly appreciate it.

Hello,

I have another conditional formatting issue in Microsoft Excel!

In a spreadsheet I'm working on at the moment I have a range of cells that I will, at times, manually give a green background. This is simply a visual aid. These cells also contain a value (a dollar amount). So, in one row, there might be three green cells and four normal cells (with no background), and at the end of that row there would be a cell that needs to calculate the total dollar amount of the cells in the row with a green background.

Can anyone provide me with instructions and information on how to accomplish this?

16 more replies

I have a spreadsheet that shows all of the upcoming events at my work (I've attached a very basic example of the way the spreadsheet is set out).

When an event passes, I'd like the spreadsheet to automatically change the formatting of the rows that contain events that have already passed.

For example, today being 14.12.09, an event for yesterday (13.12.09) would be highlighted in red to show that the day has passed.

If anyone can provide any detail on how to achieve this, I would greatly appreciate it.

Select your data, then Format > Conditional Formatting.

Condition 1; Formula is:

=$A5<$D$2

Click Format > Patterns > red colour > OK.

Click OK.

Hi Guys,

I am setting up a spreadsheet for a charter boat company, that will be used for bookings. I have described the setup below...

- Currently have a spreadsheet set up that we enter bookings on. Each day of the month is set up on a template, with a couple of blank rows under each DATE heading. We then add clients to the dates a row at a time. ie, row A contains TIME OF DEPARTURE, LOCATION, LENGTH OF CHARTER, BOAT. Each client has one row which is under a bold header for the date of the charter....(I have attached the spreadsheet if that doesnt make sense!

- I would like to be able to format it so that it automatically changed the colour of the heading row, just the first row of a particular date (as it is set to do at the moment by conditional formatting, but limited to 3!) for a number of different conditions.

I can currently get it to format a single cell based on what is in that cell, but not sure how to get it to format the entire row of that cell...

Hope this makes sense!!

Thanks Ben

What are your conditions? We can't do much unless we know what you want to format and their conditions!

Hello-Looking for some assistance on conditional formatting based on multiple cells in Excell '07. I have been unsuccessful in combining "if" and "and" statements in conditional formatting.

I basically have 3 columns. A is a text field, B and C are numeric. C is the column I would like the conditional formatting (highlighting) to happen. I have put all 11 scenarios I need to use in column C, if I could get help on one from each section I should (hopefully) be able to complete the rest.

Column A-can be "CA" or "CO"

Column B-can be 10,11,20,21,30,31,40

Column C-can be -20 to 100

IF A1="CA" and B1=40 and C1>2 then fill C1 in black

IF A1="CA" and B1=40 and C1>0<3 then fill C1 in red

IF A1="CA" and B1=40 and C1<0 then fill C1 in green

IF A1="CO" and B1=10or11 and C1>-5 then fill C1 in black

IF A1="CO" and B1=10or11 and C1=-5 then fill C1 in red

IF A1="CO" and B1=10or11 and C1=-6 then fill C1 in yellow

IF A1="CO" and B1=10or11 and C1<-6 then fill C1 in green

IF A1="CO" and B1=20or21or30or31 and C1>-3 then fill C1 in black

IF A1="CO" and B1=20or21or30or31 and C1=-3 then fill C1 in red

IF A1="CO" and B1=20or21or30or31 and C1=-4 then fill C1 in yellow

IF A1="CO" and B1=20or21or30or31 and C1<-4 then fill C1 in green

Thanks!!

you need to make sure the order is correct - not sure about 2007 but in 2003 the order was inportant and the first condition would apply

IF A1="CA" and B1=40 and C1>2 then fill C1 in blackClick to expand...

use the formula condition

=AND(a1="CA", B1=40, c1 > 2)

what colour do you want the font - black will hide the font - so you need to add a font colour

also the >2 means NOT equal to 2

IF A1="CA" and B1=40 and C1>0<3 then fill C1 in redClick to expand...

AND(A1="CA", B1=40, C1>0, C1<3)

IF A1="CO" and B1=20or21or30or31 and C1<-4 then fill C1 in greenClick to expand...

AND(A1="CO", OR(B1=20, B1=21, B1=21, B1=30, B1=31), C1<-4)

try that - i looked to see if it could be simplified just into the colours

IF A1="CA" and B1=40 and C1>2*then fill C1 in black

IF A1="CO" and B1=10or11 and C1>-5 then fill C1 in black

IF A1="CO" and B1=20or21or30or31 and C1>-3 then fill C1 in black

Click to expand...

=OR(AND(A1="CA",B1=40,C1>2),AND(A1="CO",OR(B1=10,B1=11),C1>=-5),AND(A1="CO",OR(B1=20,B1=21,B1=30,B1=31),C1>-3))

that should work for all the black conditions

i think will work - need to just try out

These are the four conditional formats - they work but i hav enot tested to make sure its all 100% as needed - so check fully before putting into production

Black

=OR(AND(A1="CA",B1=40,C1>2),A... Read more

Hi,I have got a set of data that needs to be highlighted(colour) if below the Pass Mark from each student row ( eg John- reading and Writing will turn Red because below te pass mark). I can only do one row by using the Rule but i have thousands of students, do i need to setup one rule per student? is there an easy way to do this. Many thanks !!Name Pass Mark Reading Maths Writing etc John 120 110 130 110Mary 100 90 120 130Steve 90 80 80 80

More repliesI have a chart with days across row 1 and dates across row 2. Down column A I have names. I would like to format the columns so TODAY is yellow, all future days are white (unformatted), passed days are light grey and weekend days ("Sat" and "Sun") are dark grey. I have formatted the columns with formulas such as "=B2=TODAY()" for yellow and only the top cell turns yellow, and the same for all of the other colors. What am I doing wrong?Thanks, Daniel

You did not mention Conditional Formatting. Did you try it?

7 more repliesI am trying to format the cells of my sheet to change color according to todays date and the date included in the cell. I am tracking treatment plans and when they are due/expire. so, each cell contains a name and a date: ex: Jane Doe - 6/7/10 . Currently I am changing colors of the cells manually, but I know you can use conditional formatting to do it. In my example, Jane Doe's cell would be green (as today is 5/13/10) because it expires in 30 days, however June 7th, the cell color should change to red. When the tx plan is turned in the date will be changed to 9/7/10 and the color changed to yellow. How do I do that???

Post an example of your data, which cells have what data, use the PRE tags so everything lines up.MIKEhttp://www.skeptic.com/

2 more repliesI have 4 columns - column C has the organisation compliance figure to be met by departments for certain training and column F has the formula to calculate from columns D and E where a department within the organisation are currently at e.g. =SUM(100-E3/D3*100). I wish to expand the formula in column F to fill cells Green for meeting compliance and Red for non compliance. What formula could achieve this? Conditional formatting has not worked as the figures in column C vary - is there a formula to determine what cells to format?Assistance appreciated MM

re: "I wish to expand the formula in column F to fill cells Green for meeting compliance and Red for non compliance."You can't change a cell color via a formula in a cell. The only place you can use a formula (or "Rule") to change a cell color is via Conditional Formattingre: "Conditional formatting has not worked as the figures in column C vary "That's the whole idea behind Conditional Formatting. As cell values "vary" the conditions are met or not met (TRUE or FALSE) and the either the formatting is applied or it isn't.You gave us a formula, but you didn't tell us what is in Columns C, D or E, so it's kind of hard for us to tell you what to do with Column F.What is the relationship between the values in Column C and F and how do they determine whether an organisation is in compliance or not?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesHow to use conditional formatting in excel to project an anual inspection 90, 60, and 30 days using green, yellow and red to alert me prior to the due date .

See if this thread helps:http://www.computing.net/answers/of...MIKEhttp://www.skeptic.com/

6 more repliesHey, I'm trying to compare different suppliers with Excel. The columns stand for suppliers and the rows for products. I want to use conditional formatting in a way that in every row the lowest price will be marked. I'm able do to this for one row, but when I try to apply the formatting in each row by dragging, it marks the lowest price in the whole range! How can I make this work for each and every line?Thanks, Al

I had no problem doing what you asked.I started with this table:

A B C D E

1 S1 S2 S3 S4

2 P1 4 3 2 1

3 P2 1 2 1 3

4 P3 4 1 3 2

5 P4 1 2 3 4

1 - Select B2:E22 - Home Ribbon....Conditional Formatting3 - New Rule4 - Use a Formula...5 - =B2=MIN($B2:$E2) (Note the use of the $ for the columns only)6 - Format...Fill...Green7 - OK...OK8 - With B2:E2 still selected, click Format Painter9 - Drag your mouse over B3:E5 and releaseThe 1's in each row were shaded in Green.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi,

I'm new to using conditional formatting so I need some help.

What I'd like to be able to do in the attachment after highlighting the cells green is as follows:

1. The relevant quantity in the cell in column A to be inserted into column C

2. That days date to be inserted in the cell in column E

So basically all I need to do manually is the highlighting and conditional formatting does the 2 steps above.

With Excel 7s conditional formatting, is it possible to change the color of a range of contiguous cells in a row based on the the value in one cell in that same row?

Whitola

Tech Support Guy System Info Utility version 1.0.0.1

OS Version: Microsoft Windows 7 Home Premium , 64 bit

Processor: Intel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz, Intel64 Family 6 Model 23 Stepping 10

Processor Count: 4

RAM: 8157 Mb

Graphics Card: Intel(R) G45/G43 Express Chipset, -241 Mb

Hard Drives: C: Total - 703174 MB, Free - 602896 MB; D: Total - 12126 MB, Free - 2210 MB; J: Total - 476937 MB, Free - 340925 MB;

Motherboard: PEGATRON CORPORATION, Eureka3, 1.02, 101777200000014

Antivirus: ESET Smart Security 4.0, Updated and Enabled

Try it out, I hope it will help you

http://www.spyjournal.biz/excel/conditional_format_Excel2007

as you can see from the attatched file, i have used conditional formatting to change the colour of a range of cells based on an entry made in cell E3,(with many thanks to ENT for his help), what i would like to do now, is be able to change the actual colour used based on an entry made in C3, the entry made in C3 would only change the colour used not alter the conitional format used for the entry made in E3, can this be done, or is it easier to put vba code within the sheet.

I'm looking for a conditional formatting rule for Excel.

Basically, I have several columns for grades of a test.

Column A - # Wrong (this is entered by me)

Column B - Number right (basic formula - based on Column A)

Column C - Percentage (Basic formula - based on column B)

So basically I go through and enter the number wrong and the other columns pop up the correct numbers. I have all the rows of each column configured correctly, but I want to hide/blank Columns B and C until the number is entered into the respective Column A.

So lets say A1, B1, C1.

I want to have the fomulas in B1-B30 and C1-C30 ready (simple to drag from B1 to B30 and C1 to C30), but I don't want any info there. I want those columns blank until I enter the number is A1-A30.

Is this possible? Am I even making any sense? I think I'm starting to confuse myself.

Just take your formula, and then wrap it in an IF() statement where the test condition is "A1 is not blank" (or whatever row you're on).

I have a spreadsheet where I want to format certain cells based upon the value(s) of another cell.

ie: I want A1, B1, and C1 to turn "red" if cell D1 is less than 10, but turn green if D1 is greater than or equal to 11.

I know how to do the conditional formatting so that cell D1 would change color, but not sure how to make other cells do the same.

Why not apply CF to the range rather than the cell? I don't know if that's possible, but it might be worth a shot *shrug*

chris.

Struggling in Excel as I need to use moer than 3 conditionals.... I've heard that there's an add-inCan anyone advise on where to find it and if it's any good?!Thanks!

I have a sheet which has records date of training, both ones that have been completed and ones booked for future dates. What I would like to do is have the cells with future dates in formatted in a different colour eg red. What formula do i need to do this?

I have a report and I'd like to filter only the newest occurrences of distinct actions for people. I've figured out that I can use conditional formatting with the rule =COUNTIF(B$1:B1,B1)=1 where column B contains the actions I'm trying to count. This is all well and good when the report only contains the one person I need to find newest actions for. I was just asked to filter only newest actions for all persons at an account. Is there a way to have excel count the newest unique actions for column B, and then start counting again when column C (ie the persons name) is different?

I hope that makes sense.

Using Excel 2003 I know how to do conditional formatting on one cell, but is it possible to format several cells using the value of just one. I.e. is it possible to make a range of cells in a row switch to red font when one cell in the row has the value "PAY IN"?

Yes no problemSelect all the cells you need to format then condition format - Formula is.Noldi

10 more repliesI?m trying to help a friend set up an excel workbook to assist him in keeping track of invoices that have been paid or are outstanding. He runs a Care Home and has 30 residents, some are paid for by the Local Authority (every 28 days) and some are paid for privately (monthly). Unfortunately most fees are different, and change annually; perhaps I could have a cell that we could put the different fees in, and change this as and when necessary. Most payments go into the bank directly. We are thinking of starting a workbook for every year, and 12 worksheets (one for every month) plus a summary sheet. On each sheet the columns across the top would be the days off the month i.e. 1 to 31, and the rows would be the people?s names.I want to set up, perhaps conditional formatting ?, so that as a payment is made, the amount would automatically be put into a cell that has a total for that person, and an figure taken over to the summary sheet.Also if a payment is overdue, the cell would highlight to a red fill. Again, perhaps conditional formatting ?Someone has suggested using a Macro, but I have never used macros and have no idea what to do.Can anyone suggest a way forward with a way of setting up a BASIC format, which can be tweaked up as time goes by.Sorry if I'm prattaling on, but I'm not sure how to get going.Many thanxDD

So, let's say that names are in column A, presumably a payment would be entered in one of columns B:AF, depending on the day of the month. Or do you mean something like enter an X in a cell when Fred's payment is made and then have Fred's individual charge appear in, say, column AG?

5 more repliesI have a spreadsheet that is basically measurements of a whole list of items over time so they each have one row and the number of columns per item changes based on the number of measurements taken. Each measurement fills 2 columns: one for the date and the other for the measurement. Say these are measurements of plants and there is a date that there was one kind of fertilizer added at the beginning of each row and then a second date right next to that for when someone sang to the plants. I want each set of measurements to be formatted so that if the measurement was taken before the first fertilizer date it should be red, if it is between the fertilizer date and the singing date it should be black, and if it is after both it should be blue. I know this is confusing but please try to help!

You can simply add conditional formatting to do just that, instead of the value you choose the formula option and then watch your syntax. If you run Office 2007 you have more than 3 conditions so keep this in mind.

HiI am trying to make a spreadsheet where payments are recorded, and if overdue then use Conditional Formatting to colour the cell in red. The sheet is monthly, with dates running across the top (columns) and names running down the rows. I am getting rather confused in the depths of Conditional Formatting !!I am running Excel 2007, and Windows 7Any help appreciated,ThanxDD

Have a read of this thread, it might point you in the right direction.

3 more repliesI need a formula to have a cell format a certain way when the word "YES" is entered into a different cell. Hopefully this can be done.

Here is a print screen of the situation. Any help would be greatly appreciated. I'm slowly trying to teach myself excel and I just can't wrap my brain around this one.

Thanks!

I want to shade rows on a spreadsheet which have no value in a specific cell.I know I need to use conditional formatting, but I can't figure out an appropriate formula to use. Can anyone advise?Many thanks,Gary

I have a spreadsheet that I have created a number of rules in.

I am tracking dates for expiry, and highlighting accordingly.

The trouble I am having is with highlighting dates that are within 6 months, or 180 days from TODAY(). (Only up to today(), not after).

The exact reason: I am tracking employees that have been with the company for 6 months or less, and highlighting hire dates green.

When I try to apply the formula =TODAY()-E3<=TODAY()+180, it highlights all cells, not just the dates within 180 days from TODAY().

Also, is there a way to flag names with highlighting when an expiry date is near? Right now, I have the cells that have expired highlight red, and the cells that will expire within 30 days highlight yellow. I would like to highlight employee names if they have the highlighting I've explained.

I appologize in advance if my explaination is confusing...I think this spreadsheet is melting my brain ;-)

THANKS FOR ANY HELP ON THIS!!

Hello everybody,

I'm trying to get some conditional highlighting working but I'm not sure how to go about it. For background info, the data I'm working with are inventory levels and reordering quantities.

In column K I have weeks worth of inventory a particular person has on hand. Column M lists how many units of that particular item are on order for the next delivery. The end goal is to highlight the lines where someone is ordering an item that they don't need.

So my conditions are: IF K > 6 AND M > 0 THEN highlight the whole line whatever color.

Highlighting the whole line isn't a deal breaker - if it only highlights the cell in column K and M that's OK.

Anyone know if this can be done, or am I limited to one IF statement in conditional formatting through Excel? If Excel can't handle this, is there another program I should consider using?

I hope that I communicated this clearly - if more info is needed just say so and I'll try to elaborate.

Thanks a lot!

Hi - I've tried every combination of conditional formatting, but can't work this out:I've got 2 columns, Q contains a date when a payment is due, in the format 20/06/10 and S is the date the payment is received.All I want to do is to get the relevant cell in Q to change font colour when I enter the date in the corresponding cell in S.I would have thought that if Q is not equal to a blank cell, then that would work, but it doesn't.Any help available, please?Thanks

Select the cells in column Q. Assuming that you are starting in row 1 use the CF formula=S1<>""set a format and OK your way out.

2 more repliesI want to set up the following conditional formatting in Excel:For each row, where column D contains "Yes" and Column E is blank, shade the cell in column E yellow. Where column D contains "Yes" and column E is not blank, do not shade column E.Can anyone advise me of an appropriate forumla?Thanks,Gary

Enter the following in the Conditional Formatting dialog box:Formula Is =AND(D1="Yes", E1="")then select your required Format (Format | Patterns pick a colour).

10 more repliesI am creating a table for work, I order parts for a mechanics shop. My table is basic, it has a columns for prices, vendors and status. In my "Status" column, I have a drop down menu with the following options: Researching, Pending, Ordered, Shipped, and Complete. What I am looking for is, when I choose an option in the "status" column I want the entire row to change colour. EX: If I choose 'Pending, in row 15, all of row 15 changes to blue, and if I choose 'Shipped' in row 13 all of row 13 turns green. I know how to do this row by row with conditional formatting, my problem is I want to do it for the entire table which has 500 rows. Is there a way to apply to rule to the entire table without having to go row by row?

More repliesI am trying to change the color of my cell if the data entered in in a specific range. I have four ranges in each cell I need color for. I have searched and read several articles about similar issues, but for some reason my computer screens do not have the same options or words that I am seeing in the articles. Please help if you can.message edited by mmilly

The mention of "Win 8" is probably not relevant to your question. What would be relevant is the version of Excel that you are running. Excel 2003 looks very different from 2007/2010/2013. The last three look a little different from each other, but not so much that they "do not have the same options or words" for the various features. 2007 is the version in which MS Office first introduced the "ribbon" style for the menus. Those ribbons, and the features available, will look very different than the same feature in 2003.For example, if you are reading articles related to Conditional Formatting written for Excel 2003, the "options or words" will definitely be different than the "options or words" for Conditional Formatting in an article written for Excel 2007 or later.That said, what we would need to know is:1 - What version of Excel are you running?2 - What exactly are you trying to do?3 - What "options and words" are you seeing in the articles that you do not see on your "computer screen"?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

11 more repliesIn column A of Excel i have Due date and actual completion date in Column Bwhat i am looking for is for the formula whereif actual completion date is on or before due date I need "completed" in column Cif completion date is greater than due date then " delayed completion"if completion date is blank not completed

You do not need Conditional Formatting for this, just a formula.With your data like this:

A B C

1) Due Date Completion Date Status

2) 03/20/2016 03/23/2016 Delayed Completion

3) 03/20/2016 03/20/2016 Completed

4) 03/20/2016 Not Completed

Put this formula in cell C2 and drag down:=IF($B2>$A2,"Delayed Completion",IF($B2="","Not Completed","Completed"))See how that works for you.Also, Excel questions are best asked in the Office Software forum.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

Hello, I am trying to apply conditional formatting to a spreadsheet so that a row will change its style when either "yes" or "No" is entered into a column. I can make the conditional formatting work when it is equal to a numerical value but cannot seem to work out how to make it apply when "No" is selected. Any help greatly appreciated!

I have a spreadsheet with training dates, using conditional formatting I would like to enter a formula to highlight a cell when two years have passed since the last training date.Hope that makes sense!Thanks

I can give you a bit of aroundabout way but im sure there is easier.=Today() in a cell then minus that from the dates that refer to the traing date and format that when the answer is more the 730 (365 x2)Noldi

4 more repliesI have a spreadsheet for organising my friends hen night. I want to play around with a conditional formatting option, i.e Column A is the name, and Column D is left with either a date or a simple yes to the deposit being paid. I would like the appropriate persons name in Column A to turn Green when date/or YES (whichever is better) is placed in Column D. I can then hopefully copy this to show that the person has fully paid their outstanding balance when Column G = £0. Not sure whether I have the right things in the right place or whether I need to set this up with new values. and also a newbie to excel. More just playing around really.

Conditional Formats are applied when the rule (or formula) evaluates to TRUE.So something like =A1="YES" would work for a cell containing YES.Checking specifically for a date can be a bit more cumbersome since there isn't anything like =ISDATE(A1) built into Excel.How important is it that you Format Column A based on whether or not a cell contains a date?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more repliesi have 2 columns of numerical data (1 quote from each company) i need to conditionally format the cells (highlight yellow) to show me who has the lowest quote. can somebody give me an idiots guide how to do this?thanks for any help

Say your data is in columns A and B starting at Row 1.Click in A1, Format, Conditional formatting. The first box should say Cell Value is. In the second select Less than, in the third enter =$B1 click Format and select the formatting you want and OK your way out. In B1 do the same but enter =$A1.Click in A1, click the Format Painter (paintbrush) on the toolbar, select the rest of column A to copy the formatting down. Do the same for column B.

2 more repliesI am creating a in which I want the background of a cell to be conditionally formatted to turn green when the cell contents begin with certain text but are followed by any other text.For example, I want all cells that being with "sample" to be highlighted green, regardless of whether the cell says "sample 1", "sample 2" or "sample X".Can anyone tell me how to do this? I'm using Excel 2000 and tried entering conditional formatting of cell value = "sample *" but this doesn't work.

Select Formula Is on the drop-down then enter a formula like:=LEFT(A1,6)="Sample"

3 more repliesHi - I'm keeping a list of invoices prepared, with the dates they are sent out. Can I format the adjacent cells in the next column so that when the due date is reached (date sent plus 30 days), the cell changes colour?There aren't very many invoices and although I can do something useful in our accounts package, a single spreadsheet with visible reminders would be very handy!

Select Formula Is and use =TODAY()-A1>30Adjust the reference to A1 to the correct column and row. You can then use the Format Painter to apply the format to a range of cells.

8 more repliesI need to highlight cells with dates in a spreadsheet that are more than 14 months old. I cannot figure out the formula to do so. For example: the date in B1 is 8-APR-11 (written in the formul,a bar as 4/8/11) I want it to be highlighted red if it is older than 14 months from today. ( I have a few cells that have 7-MAY-10 in them, so those should be highlighted.)

Here is a =DATEDIF() solution:=DATEDIF(B1,TODAY(),"m")>14Here's how to use it:Conditional Formatting 20071) Select your cell or range of cells. IE B12) On the ribbon click Conditional Formatting3) Click on New Rules, it?s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =DATEDIF(B1,TODAY(),"m")>146) Click on the Format button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKMIKEhttp://www.skeptic.com/

2 more repliesAt the moment I have conditional formatting in place, so if the date in the cell is less than today, the text turns red. What i need in addition to this is, if another cell is populated with say a date or some text, then it reverts back to black. This way I can easily see which records will need chasingmessage edited by Emmykav

How are you applying the Conditional Formatting?Did you use a formula?MIKEhttp://www.skeptic.com/

5 more repliesIs it possible to format cells to a certain colour if the date given in an adjacent cell is a Monday? I have a table of values occuring only on weekdays, and would like every Monday to be highlighted to mark the beginning of the week. The dates are currently in Short Date format (dd/mm/yyyy).

Assuming the actual dates are in column A, try this for your conditional formatting formula:

=WEEKDAY($A1,2)=1

I'm trying to create cell formatting when a seperate cell value reaches a specified value it will change to red.Ex. Applying custom format to cell B2. "If B1 is less than 1 FILL RED"Ideally I'd like to chain this in to 3 opitons.I really want to FILL particular rows in a table based on the first cell in that row.Ex. Row2 has a value of 5 make it one color, Row3 is 3 make another and Row4 is 5 make it the same as 2.

Well, your description is a tad confusing, but I'll give it a shot:I assume you know how to apply Conditional Formatting in 2007. if not, review Response # 4 this thread.Let's say your 3 row table is in B2:D4.Select B2:D2 and follow the steps to apply a formatting rule. This is the Formula (rule) to use:=IF($B$2=5, 1, 0) or =IF($B$2=5, TRUE, FALSE)Pick a Format.Follow the same procedure for B3:D3 and B4:D4 using:=IF($B$3=3, 1, 0) etc.I hope that helps.

4 more repliesI have setup some simple conditional formatting to change the color of a cell if it is equal to or greater than the cell next to it. Now I want to copy this format down to the 300+ cells below it. When I do this it always looks back to the orignal cell and the value in it. For example I have: if B3 is greater than or equal to A3 turn the cell B3 red which works fine. Now I copy the format of B3 down to the cells below it still looks at the value of A3 even though I am in B200, I need it to be looking at the value in A200. I have tried using the SHIFT and CONTROL keys while copying the format down but it still does not work. Anyone have any ideas for me??

Thanks

Hi,

I am currently working on a spreadsheet which is using a traffic light system which is going to highlight fields a certain colour once a certain amount of days have been reached.

I have managed to get one line of this working, but is there a way to copy and paste this to the entire sheet - because everytime I attempt to do this the formatting will continue to relate to the original rule

The 3 formulars I'm using for the traffic light system are:

A4=$E$4<10

A4=IF(AND($E$4>10,$E$4<20),$E$4,"")

A4=$E$4>20

I have been able to copy this so it fills in the rows A4:E4 but this formatting needs to go all the way down to A83:E83 so that each row works independent of each other.

Currently when I copy and paste the format whatever I input into E4 affects the entire spread sheet while if I input a date into E10 it has no affect on its row or any of the spreadsheet whatsoever.

I am sure this is something very simple I am overlooking

Please help...it is driving me mad!

DomElmfield,

Here is a macro I use to apply formatting. It is handy because it is not limited to the three conditional options, can be used on any range you add- edit in the code, and uses the change event for the worksheet so each time you initiate the change event it checks and updates the format.

I did not make any edits related to your case, you can edit the if statements to fit your needs. You can also add additional statements if needed. The macro is only an example of the syntax to perform the formatting.

Macro:

Sub Worksheet_Change(ByVal Target As Range)

Set myplage = Range("B2:F14")

For Each cell In myplage

If cell.Value = "1" Then

cell.Interior.ColorIndex = 3

End If

If cell.Value = "2" Then

cell.Interior.ColorIndex = 46

End If

If cell.Value = "3" Then

cell.Interior.ColorIndex = 6

End If

If cell.Value = "4" Then

cell.Interior.ColorIndex = 4

End If

If cell.Value <> "1" And cell.Value <> "2" And cell.Value <> "3" And cell.Value <> "4" Then

cell.Interior.ColorIndex = xlNone

End If

Next

End Sub

You could also use case statements, but that is another thread.

Stoneboysteve

The old versions of Excel were easier than this version 2010. I am trying to either code or conditionally format a column whereby if the date is:

< Today's Date = Red

> Today's Date = Green

= Today's Date = Amber

The column where the dates are is Column H and the dates start on row 2.

I have also tried to insert a module with a case statement thus:

Sub Macro1()

Dim i As Integer

i = 2

Do While Range("H" & i).Value <> ""

Select Case CDate(Range("H" & i).Value)

Case Date 'Todays Date

Range("H" & i).Font.Color = vbRed

Case Is < Date 'previous Dates

Range("H" & i).Font.Color = vbBlue

Case Else 'Future Dates

Range("H" & i).Font.Color = vbGreen

End Select

i = i + 1

Loop

End Sub

but to no avail the workbook tells me I will lose the macro if saved in this format (i.e. xlsx) which should be the correct format.

I have tried and tried but to no avail - can anyone help me please?

Hello. I'm having some problem with conditional formatting rules in MS Excel 2010. I have a worksheet with 2 existing conditional formatting rules.

I just added this rule

=IF(AND($L5<(DATE(2010,10,1)),$S5<>"Active"),TRUE,FALSE)

Problem is once I click Apply, the whole worksheet turns pink. For some reason, the rule isn't meeting the condition before applying the formatting.

What could be the problem.

Need some help with this.

Thank you

what range have you applied to ?

otherwise it looks OK

what happens if the condition in L5 / S5 makes the condition true or false

i have a spreadsheet and basically what I want to do is if a certain cells equals to a name, then I want that that row to change to a color thatI assigned to that name, which is blue. I already have done done but what I need help with is that if the other on that row doesnt have a value, then I want it to stay white color but if I add a value to a cell within that row, then I want it to turn blue. Please keep in mind that I have 7 names that I've assigned a color to that name.

I have a conditional formatting question. I need to compare dates and depending on the result, come back with the correct formatting. This will be using three possible columns including Due Date, Revised Due Date, and Done Date.For example, if a project is logged as being due on 6/9/12, and the done date is 6/9/12, I want to format the cell to have a green background. Or, vice versa if the done date is after the due date I want the cell to automatically format with a red background.I also need to account for if the done date matches the revised due date but not the original due date.Is something like this even possible?

Have you tried using the greater than, less than and equal operators to compare the dates? All you are looking for are formula that return TRUE when your conditions are met. Whenever the formula returns TRUE, the CF will be applied.e.g.=A1>B1=A1<B1=A1=B1Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more repliesHi,

this is my first post on techguy. I need some help with a conditional formatting, the sheet that i'm working with has cells that contains '2 Y' and '-2 Y' I want to color any cell with a positive number and 'Y' to be green, ex: (2 Y). Any cell with negative number and 'Y' to be color red, ex: (-2 Y). I did a condition where highlight any cell with 'Y' to be green, what condition can i do for negative value?

This attempt was done in 2003 and needs some tweaking when cell is blank.

I have worked on the premise that "Y" will be constant in all calculations.

Could not get A3 to remain without the red colour even though B3 is blank

Also worked on the pemise that to obtain the "positive y" or"negative y" was along lines of formula in A1 .

Need to go out at the moment so will leaave it to the experts to consider the attached spreadsheet

Pedro

I can't figure out how to do in 2007 what I could do in 2003. I'm trying to fill cells a different color based on the age of the date in the cell. For example, it will fill yellow if it's 6 month old or older, and red if it's nine months old or older. However if the cell is blank, I don't want it to fill at all. Here is the formula I am currently using, please help: =(D1<=TODAY()-330)