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

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

you could try doing an AND and not equal ""

like

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

see attached

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

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.

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

=ISBLANK(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,0))

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

=IF(C14="","",IF(ISERROR(SEARCH("*Platform*",C14,1)),"N","Y"))

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?

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.

Rollin

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

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.

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.

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!

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.

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,

Debbie

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.

HTH

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.

Hello,

I have worked with this formula:

Code:

=(SUM(--(YEAR($D$5:$D$24)=2012)))=2

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!

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.

Hi TSG,

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.

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

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

IIf(Date()-[tblRemarks]![Date]>7,[frmIntro].[Title].BackColor="vbBlue",[frmIntro].[Title].BackColor="vbRed")

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

and

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?

=and(g16<10,w16>10)

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

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.

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

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

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?

Thanks

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

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?

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?

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)

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

However it does not work.

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

Thank you very much

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.

=IF(DAYS360(TODAY(),S5+90)>45,S5+91,IF(DAYS360(TODAY(),S5+90)<=0,"PastDue",IF(DAYS360(TODAY(),S5+90)<45,"Due")))

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.

Chris

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.

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

Thanks!

the formula

=A2>=TODAY()

will test the date part

then

=IF(B3<>"Y",A3>=TODAY())

OR

=IF(B3="",A3>=TODAY())

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 ?

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.

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

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,

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

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

Thanks,

Farmgal

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

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.

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

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 need to conditionally format a cell based on the outcome of a formula and the value in another cell and I’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...

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

how do I write an expression ti conditionally format a report field, called Copy of New report.

In the field Dia @ last turn, If any of 30A,30B,30C,30D=<720 then highlight the value in red and if either of 40A,41D=<730 then highlight the value in red

Thanks

Ben

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.

Example:

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"

=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:

=A1="N/A"

because the "return" must be TRUE or FALSE.

(works for me, anyway )