Forum Discussion
Conditional Formatting
Hi, I have changed it so that ALL records less than today (+ optional n) shows as RED.
Kind regards
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.
;-)
