Over 1 million tech questions and answers.

Solved: Conditional Formatting: Ignore Blank Cells in Conditional formatting

Q: Solved: Conditional Formatting: Ignore Blank Cells in Conditional formatting

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
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!

Preferred Solution: Solved: Conditional Formatting: Ignore Blank Cells in Conditional formatting

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.)

A: Solved: Conditional Formatting: Ignore Blank Cells in Conditional formatting

Read other 9 answers

I am trying to highlight specific cells in a row that meet a set or a single criteria. Unfortunately, there isn't an easy way to skip blank cells in executing the conditional formatting. I am able to get the criteria to work, but blank cells get highlighted. When zero is part of the criteria, the blanks come into play. In Excel 2007, other versions operate slightly differently.

Take for example a row of 10 cells/values, example:
175 182 blank 210 220 237 blank 314 124 120

Example of criteria:
a) below 200
b) between 200 and 250
c) above 250

or single criteria upto 250

When ever a 0-250 or below 250 criteria is applied, blanks get highlighted.

Can someone suggest a simple formula to make Excel ignore or skip consideration of these blank cells.

Thanks in advance

A:Conditional Formatting to ignore or skip blank cells Excel 2007

you could try doing an AND and not equal ""



see attached

maybe a better way to do it and one of the excel gurus may provide

Read other 1 answers

I'm having issues with the following. It seems simple to me but can't seem to get things going right.

What I'm looking to have happen is in Cells C6 through C32 (all of which have a formula - which is what is causing me the grief I think) is when the cell is less then 0 colour green, when the cell is greater then 0 colour red and when the cell is 0 or empty do not colour.

What I have makes the greater then % go red, the less than go green and the 0 stay white. it's when the cell is blank that my problem happens. I also want this cell white when there's no visible number.

If I do just the two formatting of greater and less then the blank cells stay red when I want them no colour. So that's what I'm stuck on. Any Ideas? I've trolled around other sites and tried different things including posting to another site with no immediate luck. This site might be a little better as I can upload the file.

Thanks in advance.

A:Solved: Conditional Formatting - Blank Cells are red when empty

Read other 9 answers

I have a conditional format on cells to remain white when the cell is blank using this:


However in some of the cells I have the following formula:


I need my ISBLANK to treat the cells where the result is "" as blank cells. Visually they are blank, but not in Excel's eye So the column that has that formula in it moves on to the next conditional format and fills the cell grey. This greying should only take place when a cell has an actual value.

Can anyone help?

A:Solved: Conditional formatting isblank to ignore formulas

So instead of testing for blank or empty string "" value why don't you evaluate the length of the cell using the LEN function? If the cell contains a formula only the LEN function should return a 0 value. If the formula returns an actual value the LEN function should evaluate to a number greater than 0.


Read other 2 answers


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..

A:Solved: Excel 2007 - conditional formatting copy to cells?!

Where you have


remove the $

so it reads


press apply, press ok

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

Read other 3 answers

I have a list of employee dates of births and would like to conditionally format the cells to highlight when we are six months from their retirement date assuming that date is their 65th birthday. How would I do this?

A:Conditional Formatting Cells

I am assuming you are using Excel 2007.

In which case, you select the cells you want to apply the conditional formatting to.
You then click on the Home ribbon and click on the Conditional Formatting button and then click on the "New Rule" menu choice.
In the "Edit Formatting Rule" dialogue box, select the following option in the "Select A Rule" area "Use a formula to determine which cells to format"

In the edit a rule area, type the following formula =ROUND(YEARFRAC(A1,TODAY()),3)>=64.5

Then click on the Format... button and select a colour that you want to use if the person is 64.5 (true) or older based on the formula.

Then click on OK.

You should then see the colour applied accoringly.

See attached example.

Read other 1 answers

This has more than likely been answered loads of times but maybe I am searching in the wrong place.

I have a spreadsheet in which I record expiry dates of certificates and I need it to do the following:

Less than today, expired certificates date to show RED

Today plus 14 days, due to expire to show AMBER

Today +14 up to 60 due to expire between day 15 upto 60 days,to show in GREEN

I managed to work our for one colour which gave me pre today and upto +60 days but only in RED.

Any help greatly appreciated.

A:Conditional formatting Cells Excell 2007/2010

First select the range that you want this formatting applied for.

Then create a new rule for each of the types below. I think 'Format only cells that contain' should be fine for what we need here.

1. Cell Value, less than, =TODAY()
2. Cell Value, between, = TODAY(), =TODAY + 13
3. Cell Value, between, = TODAY() + 14, =TODAY() + 60

The 2nd one is +13 to account for the overlap that would occur if both 2 and 3 were set to +14. As you can see if they were both +14 both rules would become true and you wouldn't have an easy way to control which one was shown.

Hope this helps!

Read other 1 answers

I've got a spreadsheet that contains a row cell for each day of the year. Now, each of these cells contains a dollar amount, except, of course, for the cells representing days of the year still to come. Those cells contain an "#N/A" reference (which they need to so as to not disrupt the chart pulling from the same data).

In this row there is conditional formatting set to highlight the top ten entries for that year (so, the top ten highest amounts). At the moment, though, each one of the cells excluding the cells with the "#N/A" value is highlighted, and I believe it's because the range of the conditional formatting extends to the full row thus including the cells with the "#N/A" value, which makes the conditional formatting rule unable to work out the top ten entries.

(I hope this makes sense so far...)

So, the reason why all of these cells already have data in them that causes the "#N/A" value is because they are pulling data from another spreadsheet and (saving me from having to re-type entries multiple times each day).

So, essentially, I just need the conditional formatting to work even though there are "#N/A" values within the rule's range.

If anyone can help me with this, it'd be greatly appreciated.

A:Solved: Excluding "#N/A"-Valued Cells From Conditional Formatting

Read other 11 answers

I am trying to do some conditional formatting to change the back color of a tect box if the difference between 2 dates is met. Apparently there is something that I am not doing right


A:Solved: Conditional formatting

Read other 11 answers

Can anyone help me with using conditional formatting? Basically, I have about 10 cells with a yes or no question but what I want is = if cell #1 is answered " No", then I want preceeding cells to turn gray. If cell #1 is answered " Yes", then leave the preceding cells with no color.

A:Solved: Conditional Formatting

Read other 6 answers


I have a small problem, please see attachment for reference.

I'm trying to use Conditional Formatting to change the cell color of any row that has a date which is less than 15-Jul-08. i.e A3:C3 would have a red backgorund, but A4:C4 would have no fill.

Using CF I can only get it to color just the cell with the date and not the row. It also colors other info in column B that i did not want it to do.

Any help would be greatly appreciated.

A:Solved: Conditional Formatting

Read other 11 answers

I've used =RIGHT($B1,5)="Total" on a spreadsheet in Excel 2007 for several years. It won't work in Excel 2010 using the same spreadsheet. Is there a trick or does the rule need to be written differently?

Thank you,

A:Solved: Conditional Formatting

No, it hasn't changed. I would be willing to be it was a referencing issue. You don't have any conditional formatting on the workbook you uploaded, so I can't troubleshoot what you had. Perhaps a more succinct method would be to use the formula =ISNUMBER(), because if you have numbers as your values, and in your total cells you just have number and the word " Total" following it, those cells with the text following the number will be seen as text.

Now, that will solve your problem. Although I wouldn't recommend you having it setup that way. It could be problemmatic down the road. I would recommend having all your data in a Table, then summarize with a PivotTable. Either that or use Subtotals, if you don't want to use a PivotTable. But it will be easier than hand typing it in.


Read other 2 answers

Im using a sheet as a project programme (dont have ms project)

the conditional formatting is to automatically colour cells given dates

formula: =AND($P10<=V$5,$S10>=Z$5) - formatted to go blue if true
col. P is start date
col. S is end date
row. 5 is the beginning date of each week

if there are no dates at all, the top row gets coloured in

if there is any date in col.P the last cell in that row get coloured in
any ideas why guys?
thanks in advance.
p.s. my next step is to then also be able to control the colour by way of multiple conditions dependent on a cell in that col. stating Red or Blue etc. Ty.

A:Solved: Help with Conditional Formatting

Read other 6 answers

Hi All,

There is a workbook in which there are two sheets, in sheet 2 I have put a formula in column F as =TIME(HOUR(sheet1!F3)+1,MINUTE(sheet1!F3),0) ie if F1 of sheet 1 has time as 01:00 AM then F1 of sheet 2 will take it as 02:00 AM, I also want that if no data is there in F1 of sheet 1 then F1 of sheet 2 should show red. Any advice would be very helpful.

A:Solved: Conditional Formatting

Read other 16 answers

The other day "The Villan" helped me a lot with a formula for conditional formatting and I am enjoying working with it in my spreadsheet.

Today I wanted to add to this (nothing to do with the original formula, just making more use of colours as visual help).

I would like to have the cell appear with red background if two conditions are met:
1) the actual cell value should be less than 10
2) another cell should be larger than 10
Both conditions have to be met.

I am trying to set a condition as follows:
Condition 1: If cell value G16 is less than 10 and Condition2:
Formula: =If((W16>10),"xxx", "") and put the red colour in both boxes .

The first condition works but the second must be wrong. Can someone tell me where I go wrong?

A:Solved: Help with conditional formatting


Well, nuts, it is not letting me use capital letters in the formulas.

Read other 2 answers


I have worked with this formula:
to change cell color - I need to change it to say if the sum of =SUM(YES($H$5:$H$24))>=2

I can't figure it out though, and I'm sure its very simple.

Please help! thanks!

A:Solved: Conditional Formatting

Read other 6 answers

Can someone take a look at this screen shot and tell me if I am doing something wrong. The way I have it, it is kind of working but not doing what I expect. I want to change the text box different colurs depending on the time elapsed fron the last time that [Forms]![frmIntro]![Data Changed]
They are changing colors but not in the ranges that I am expecting.

Greater than 7 days Blue
Greater than 14 days Green
Greater than 21 days Red

Maybe I am not for formatting something right??

A:Solved: Conditional Formatting

Read other 10 answers

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!



A:Solved: Excel, conditional formatting

Read other 12 answers

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


A:Solved: Excel Conditional Formatting

Read other 9 answers

Hi guys i am back
I am using 1 formula (listed below) to help me with calibration reminders but after having a discussion with my boss she would like to have the date show up in color and not wording.

She does not want to see PastDue or Due, only for the date to show up in the colors i have selected using conditional formatting.
Example: Calibration Date Calibration Due Date
8/5/2010 11/3/2010 <----- does not show up using the formula above but she would like to see the date instead.

I have =SUM(S5+92) to automatically change with a calibration date has been entered to show up in calibration due date. =IF(AF5>45, "OK", IF(AF5<=0, "OVERDUE", "DUE")) is another formula that i was playing with to help with calibration reminders. =INDIRECT("AG"&ROW())="Due" is the conditional formatting formula that i used to have the date change colors.

If there is a possible way to have this all together that would be great. Let me know what you guys think i should/can do.


A:Solved: Conditional Formatting/ Formula

Read other 9 answers

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")

A:Solved: Excel - conditional formatting

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 ?

Read other 3 answers

Can someone tell me if the following is possible. I have a very basic database, and one field on a form has the input options of "Completed" or "Outstanding".

What I want to do is change the bacground colour of a different field to green when the other field is showing as Completed.

I understand how I can change a given field depending on conditions in it's own field, but I can't see how to change conditions of field A depending on the condition of Field B.

Help much appreciated as always.

A:Solved: Access - Conditional Formatting

Read other 9 answers

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.

A:Solved: Conditional formatting in Excel

if you have set the formula up in the conditional format - to be B30 and NOT $B$30
then you can copy
paste special
and copy to A31

Read other 3 answers

I have a form in Access that uses information from 2 tables. I need some text boxes to become transparent (appearing 'greyed out') when the value of the Type text box is 'Counsel'. I am assuming this can be done done using an 'if statement' but cant seem to get the code...can anyone help?


A:Solved: Access Conditional Formatting

Read other 7 answers

A few months ago, I was very happy to get some good formula advice here, so I am back for more

For my spreadsheet and stock projections, I would like to achieve the following:

one column shows me the projected stock level based on previous sales -e.g. =AA776-(AS776*$Z$1)

This provides me with a future stock figure. Now I don't need this field to be populated in every row as some items are discontinued and in this case the negative outcome is just providing "noise".

So in addition to the above calculation I wonder if I can use conditional formatting based on text shown in another cell, e.g. if cell I776 shows text, then highlight cell Y776 (or even better delete contents of Y776). Is this possible?

A:Solved: Conditional Formatting &amp; Formula Help please

Read other 12 answers

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...

A:Solved: Excel conditional formatting

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

Read other 2 answers

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?

A:Solved: Conditional Formatting in excel...

Read other 11 answers

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

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.

A:Solved: Excel VBA -> Conditional Formatting

Read other 8 answers

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.

A:Solved: Excel Conditional Formatting

Read other 10 answers

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

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

Any advise would be greatly appreciated

Thanks in advanced


A:Solved: EXCEL Conditional formatting with AND

Read other 7 answers

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.

A:Solved: Excel Conditional Formatting

Read other 14 answers

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.

A:Solved: Question about Conditional Formatting

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.

Read other 1 answers

Dear all,

I am trying to prepare a timeplan of 30 months/7 workpackages, so that all cells are coloured red between the starting date given in a cell and the end date given in another cell.

I tried the conditional formatting "AND" formula (example: month number 3)

However it does not work.

If anyone could help, I attach here the very simple table (Excel 2003).

Thank you very much

A:Solved: Timeplan through conditional formatting

Read other 9 answers

I want to highlight a deadline date (Row A) if it has past or is equal to the current date. However, I only want the conditional formatting to be applied should the response in row B be empty (not "Y")


A:Solved: Need help with Conditional formatting 2 criteria

the formula
will test the date part

Read other 1 answers

I need to conditionally format a cell based on the outcome of a formula and the value in another cell and I&#8217;m stuck as to how to do it.

I have a table that has the next 50 weeks of our project across the top row (row 10) and subsequent "week ending" date in the next row (row 11) and two columns (columns I and J) that I enter the start and end dates in respectively (always a 5 day working week whose end date corresponds to that stated in row 11).

For each row I have used conditional formatting based on the function (=AND(L$11>=$I14,L$11<=$J14)=TRUE) to highlight the cell on row 14 for which the dates I enter (I14 and J14) fall within the given week of the project based on the ending date of each week (row 11).

This has worked perfectly as a scheduling tool up till now however I have been asked to change it. I now require that the formatted colour be one colour when the value in a separate column (column F) is 1 and another colour when the value in the column F is 2. Any help would be greatly appreciated.

I have attached the document to help if needed and please post if it's not able to be done please.

Thanks in advance...

A:Solved: Conditional formatting - Two conditions... One format

Read other 15 answers

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.


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.


A:Solved: Excel 2003 Conditional Formatting

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?

Read other 1 answers


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

A:Solved: Excel Conditional Formatting Issues...

Read other 10 answers

I'm a first time TechGuy user so here's my question. I have a column of data which contains cells with numbers and cells with text (not both together). I'm using conditional formatting to change the cells that contain numbers which meet certain criteria to red font. My problem is, how do I keep the text from changing color. I want the conditional formatting to ignore the cells containing text so they remain in black font. I don't know VBA language - I'm not that advanced. Appreciate anyone who can help me with this.

A:Solved: Conditional Formatting for numbers and text

Read other 16 answers


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
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.

A:Solved: Excel 2003 Conditional formatting

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

change the first drop down to
Formula Is
then in the next box

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

Read other 3 answers

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?

A:Solved: Excel: Advanced Conditional Formatting

Read other 13 answers


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.


A:Solved: EXCEL: odd behaviour with conditional formatting

Read other 9 answers

Am trying to CF a range where condition I want is "If cell is greater than 1", so that if empty no conditional formatting.

Have ascertained by using ISBLANK that the value in cells is "0".

Edit / Replace cannot locate any "0's" nor can it find a "$" I used to see is the replace function was working.

When I highlight the range and CF as "If cell is greater than 1" all cells in range respond where I only want the one with a real value in it.
Adjoining columns CF quite OK.

Suggestions welcome.


A:Solved: Conditional formatting hidden zeros

cells greater than 1
will not show up for 0 and 1

Read other 2 answers


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


A:Solved: COnditional Formatting In Excel 2010

Read other 16 answers

Hi guys,

I don't know VBA or SQL, I just click icons and hope for the best.
I have a form in MS Access 2007 being used as a time clock. The 3 parameters I'm working with are:

e_14-15 (a checkbox)
e_16-17 (a checkbox) and
=DLookUp("[TotalTime]","Total Time","[e_ID#] = Form![tc_ID]")

The conditions I have are (and have no idea how to syntax or even where to put):
If e_14-15 is checked AND the value of =DLookUp is between 30-39 OR if e_16-17 is checked AND the value of =DLookUp is between 40-49, then format =DLookUp with a yellow background and red text
And also
If e_14-15 is checked and the value of =DLookUp is >=40 OR if e_16-17 is checked and the value of =DLookUp is >=50, then format =DLookUp with a red background and black text.

We basically need a huge visual clue that the workers we have are about to hit or are over their allotted hours, based upon whether their respective checkboxes are true.

I have no idea where to even start with this. Any help would be greatly appreciated.


A:Solved: conditional formatting in an MS Access form

Read other 12 answers

In Excel, I have a drop Down list with the first item as "N/A" (without thr quotes) and then several other items. As an example, N/A, 1X, 1Y and 1Z. When N/A is in the top cell, I want that cell and the next lower 4 cells all Gray. When any of the other items (1X, 1Y etc.) is selected, the cells turn back to white.

I can't seem to get the syntax right for Text comparison. I get errors when I use N/A as a comparison.


Cell A1 Data Validation is: [Allow] is "List". The [Source] is "N/A, 1X, 1Y, 1Z"
Cell A1 Conditional Formatting is: [Cell Value is] [Equal to] [="N/A")], format the cell as gray.

NOTE: This works fine.

Now in Cell A2, Conditional Formatting is: [Formula is] [=IF(A1=N/A)] , format the cell as gray.

I have tried putting the N/A in quotes and without. I get an error that says "The formula you typed contains an error"

A:Solved: Conditional Formatting based on Text

=IF(A1=N/A) isn't a valid formula, period. Try it in the worksheet, you'll get the same error. It's like the story.

For your CF formula:


because the "return" must be TRUE or FALSE.

(works for me, anyway )

Read other 2 answers


I've been trying to do something for a while but i can't seem to get it right. I have two worksheets, one holds data on various tasks and their current status of completion using conditional formatting to colour code the status. The other sheet just displays the names of the tasks so by the colour coding I can see at a glance the status of each task.

I have been able to create the conditional formatting to do what I want it to do but when I reference the corresponding cells in the second worksheet the colour coding system does not appear. Is there a way of carrying this across other than manually changing the colours as this would become rather time consuming.

I have made up a very basic version of this which includes the sheets and the conditional formatting. does anyone know how I can do this?



A:Solved: Maintaining Conditional Formatting Across Worksheets

Hi Daniel

I think the attached is what you are looking for. Normally, you cannot reference data from another sheet in conditional formatting. The way around this is to define a named range for the data you need to reference. In your file I defined the status data as name "Status". Then in the first sheet I used VLOOKUPs to get the status for the formatting. I used just basic colours (I'm a bit colour blind) but if it is what you are looking for it should be easy enough for you to change. Also, I simplified somewhat your original conditional statements.

Read other 3 answers