SOLVED

Conditional Formatting

Copper Contributor

Can anyone do some conditional formatting on the attached document...

 

I need expired dates to turn red, any dates within 30 days to turn yellow, and all other dates to turn green.

 

I have tried doing it but there are a few cells where it isn't working.

16 Replies

Hi,  Try the following (attached). 

I wasn't sure what column you wanted to check so I added an additional row so you could switch checking for any column On/Off!  The box on the right controls when the records will be shown in Red, Amber or Green.

The text in the Red, Amber and Green are for info, you can change the text to whatever you want.  It's the Yellow Boxes that are important.  

Not sure if the date in G15 should be 01/06/2098 so I've marked it for you to check.

 

Hope this helps.

 

 

Hi, 

 

Thanks for you help.

 

1 problem though, cells E6 & E17 should be red as they are out of date. They currently have no fill.

 

Any ideas?

best response confirmed by rswann97 (Copper Contributor)
Solution

Hi, I have changed it so that ALL records less than today (+ optional n) shows as RED.

 

Kind regards

You are a legend!

 

Thanks for your help.

No problem, it's much easier to fix when the actual file (or example file)  is included.

;)

I have a similar problem I think.  Ultimately, I want a cell to be yellow UNLESS a set of complex conditions occur (complex in how I have to strip out certain data from one column and compare to data in another column).  I initially used conditional formatting looking for some data in a row to apply simple formatting (border, yellow fill).  Then I added another rule with the complex conditions above hoping that it would override the yellow fill with the red fill.  I played with adjusting the order in which the rules would run but that does not seem to have any effect.  Each rule works (tested) but the rule I added last will not override the first rule.  In other words, once the rule to apply the yellow fill, the rule that conducts the additional testing will not then change the fill to red.  So the complex rule also checks for some data in a row, then further evaluates data in a particular column in the row (does some date comparisons which determines if the current date is 30 days after a date pulled from another cell AND there is no currency data yet provided in that cell).  This rule needs to be applied to every row in the worksheet (applied to multiple columns).  Although I haven't yet added an additional criteria since I have to confirm the business rule that would trigger the condition, but there will have to be a trigger in a (different) cell that checks the same thing but ALSO checks to see if a particular value has been entered in that cell.

 

Keep in mind this is a spreadsheet used by users that are not Excel "experts" and I do try to use the simplest method of achieving something (to try to prevent someone from deleting data, a column, using an invalid value that is being tested, etc.).

 

What are my options?  I know I could do this easily in VB but the owner of the spreadsheet would not be comfortable owning a spreadsheet with VB in it.  Also, I work for the government and I do run into security issues trying to implement VB even though it would not be used outside my team (theoretically).

 

Same spreadsheet, different question.  Is there a "simple" way to dynamically add a formula to a column in a row when data is added to a different column (without manually copying the formula to the target column in the new row)?  Again, I could do this with VB but same concern as above.

Hi, Your first question isn't easy to answer without seeing your equations however, I'm guessing your formulas (although working individually) don't make sense together.  For example, if you have A1=3, B1=1 and C1=2 then the Conditional Formulas written separately would work...

IF A1 > B1 then BLUE (&) IF A1 > C1 then RED.   However, put together, they wouldn't work if you had them as...

IF A1 > B1 then BLUE followed by IF A1 > C1 then RED.  That's because the first criteria has been met.  You would need to change the order of the formulas to make sense...

IF A1 > C1 then RED followed by IF A1 > B1 then BLUE.  

 

As for your second question.  The easiest way to extend your formulas and formatting down -automatically, when someone adds a new row- is by changing your area into a TABLE (see attached picture).  The only other way, without using VBA, is to nest (wrap) your formula inside an equation that gives a blank result unless someone enters data onto that row.  

 

For example, say your table is only down to 20 rows, then you could put your formula inside something like...  =IF(A1="","",[your formula goes here]) and copy it down to say row 100.  That way, if column A row 21 is empty then the formula would result in a blank result.  As soon as information is placed in the relevant columns in row 21, then the formula would give a result.

 

Hope that helps.

Thanks for responding so quickly.  Your answer to my second question is what I thought I would have to do.  I'll have to give that one more thought.

 

On the first question, to ensure I understand what you are saying, I have two rules.  One rule results in changing the color of a cell to one color (essentially if the first cell in a row has any value then the cell color of another cell turns, say, blue).  The other results in changing the color of a cell to a different color, red.  The second rule would only be true under more restricted conditions.  So...the first rule turns the cell color blue when a user puts a value in the first cell of the row and only if the second rule happens to be true will that cell then change to red.  Tested independently both rules work.  I "thought" that if I change the order in which the rules executed (Manage Rules then move the second rule for execution after the first rule) but it seems that this doesn't work and honestly, not having to worry about rules that compete with one another, I didn't know if this would work.  The second rule returns false based on testing some dates in the worksheet such as if today's date is after a date indicated as a header above a series of columns and the cell has no value then the cell turns red.  So the notion was the first rule would execute and turn the cell blue as soon as a value is put in the cell in column A.  Then if the second condition returns true, the cell would turn red.  What I think may be happening is once the first rule changes the cell color to blue, the second rule can't seem to turn the blue cell red.  The reason I think this may be the case is because if I leave the first rule active (removing the second), the cell turns blue after I enter something in column A, and I then manually change the format of the cell to change the color to red, the color will not change to red.  Is there a way to get around this?

Hi MJS-B,

 

It can be a little tricky.  See the attached file which should help you understand it a little better.

It comes down to a combination of the tests, order and your rules.   There is also a little check box that you need to select.   I pulled the test out into separate cells (so it's easier to understand) but there's no reason they couldn't be inserted into the main Conditional Formatting formula once you understand what I've don.  Of course, you don't have to combine it.  The thing you'll need to adjust are the rules eg > Date or >= Date etc.  and the columns eg I've chosen column "F" and yours may be different.  Finally, remember to lock the relevant columns, rows by using the $ symbol.

 

;)

I was hoping you could assist me with a similar problem? My spread sheet has membership dates in one column and expiry dates in the adjoining, I had it set up so if the exoiry dates were exceeded, the cells high lighted with a red fill. This no longer seems to be working, could you help me please? @Nauthstar 

@johnc47 

That's a separate topic, better if you start new conversation.

As for the file, you highlight dates which are in this and previous months (Aug and Jul) of the current year - there are no such dates.

Apologies, I am new to the community thing. How do I start a new conversation?

@johnc47 

Please go here https://techcommunity.microsoft.com/t5/Excel/bd-p/ExcelGeneral and click on Start new conversation

done, thank you.

Hi @johnc47 

 

I don't think there was anything really wrong with your spreadsheet.  The conditional formatting was not showing because you had updated the memberships with new dates for this year.   

 

I have however made a few adjustments.  I've made your list a table so you won't need to update formulas when you add a new person to the bottom - all the conditional formatting will also coy down.  

I've modified the print so that the header appears on every printed page and I've also added a column which tells you the Student's age.  

At the top I've also included a couple of equations to tell you how many students you have and their average age.  These rows along with the bubbles can be deleted without it affecting your print range.

 

Hope this helps and good luck with the classes.

 

Kind regards

Nauthstar

Hi @Nauthstar 

 

Thank you for your assistance, much appreciated.

1 best response

Accepted Solutions
best response confirmed by rswann97 (Copper Contributor)
Solution

Hi, I have changed it so that ALL records less than today (+ optional n) shows as RED.

 

Kind regards

View solution in original post