I have an estimated finish date and an actual finish date with the actual finish date summing up a number of numbers across 6 cells and adding them to a start date to determine the actual finish date.
What I am wondering is instead of using conditional formatting to change the cell colors from green (on time to + 3 days, amber + 4 to 8 days, and red for 9 days +) is there a formula that can be used instead of conditional formatting.
Short answer - no, regular formulas cannot change cell colors.
Why would you not want to use CF?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thought it might be easier to lock down a formula but am OK using CF. Wasn't sure if there was another way.
There's always VBA, but that would be overkill here, I think.
What you COULD do is have a separate column that displays a message based on a formula. You could then simply colour the column itself or another based on the word that appears there using CF. The calculation column could be hidden. This way, you would not have to tinker with the CF rules, but could easily change the formula driving them. Does this make sense? It's a compromise.
EDIT: Having just re-read my post, it's probably as clear as mud, so shout if you want me to show you wnat I mean!
Last edited by AliGW; 07-29-2018 at 03:48 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
So, I bit the bullet and put together an example workbook (attached). Two possible compromise solutions, both using a helper column, but using it differently.
The first uses a helper that could be hidden. The CF rules are based on the word that appears in the helper column.
The second uses the helper column as a flag and again the CF rule is based on the word in that column. In this solution, the CF colours the cell and the text in it the same.
Excel 2016 (Windows) 32 bit
Estimated Actual Helper Estimated Actual Flag Green Green Green Green Green Green Green Green Amber Amber Amber Amber Amber Amber Amber Amber Amber Amber Red Red Red Red Red Red Red Red Red Red Red Red Red Red
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
=IFNA(LOOKUP(B2-A2,{0,4,9},{"Green","Amber","Red"}),"")
Sheet: Sheet1
Last edited by AliGW; 07-29-2018 at 03:18 AM.
Hello,
You could work around on Custom Number Formats for three colours, eg,,
[Red]$#,##0.00;[Blue]General;[Green]-$#,##0.00
The result or formula would need to be positive, zero or negative to trigger the text colour changes
Not strictly true,Originally Posted by pike
[Red][>9]#0.00;[Green][<=3]#0.00;[Yellow]#0.00;@
But this would only change font colour, not cell colour as originaly asked.
Last edited by jason.b75; 07-29-2018 at 05:30 AM.
Hello,
Consider an alternative
In a helper column you can change the Key Word and colour via custom format like [Red]"Red "#;[Blue]"Blue "#;[Green]"Green "-#
you can now use a formula in this cell to trigger colour and Word
This can be done with out CF
@ pike. You might want to give a better explanation if you think that your suggestion might be useful to anyone. Crude oil is clearer than your post.
if the op wants an explaintion they will get it
I was asking for the benefit of the OP.
A helpful suggestion should contain enough information that the OP would at least be able to see something that might be useful to them.
edit:-
In addition, have a look at rule 4
Any help provided should be benificial to all members and guests browsing the forum, not just the OP.
Last edited by jason.b75; 07-29-2018 at 06:26 AM.
Please add an explanation for the benefit of all and to make the thread useful to others, not just the OP.
I would like to understand the solution you have offered, too, by the way.
Hello,
Its just an alternate option
Formula =IFNA(LOOKUP(B2-A2,{0,4,9},{0,4,10}),"")
Format Cell > Number > Custom > Type > [Red][>9]"Red Alert ";[Green][<=3]"On Time ";[Blue]"Warning ";
Thanks everyone for the solutions. Been offline for a day. will work through each of the options now. I'm sure I will have more questions.
Greatly appreciate the input.
Thanks again
FBS
You’re welcome!
I am trying to add a spreadsheet example with some notes that I hope will clarify what I am trying to achieve. Not sure what I am doing wrong that I cannot upload.
Hopefully when I can upload this will provide some clarity.
Many thanks in advance.
FBS
For another explanation of Pike's suggestion, see John McGimpsey's site here:
http://www.mcgimpsey.com/excel/conditional6.html
It shows a way of getting up to 6 conditional font colours when Excel was limited to 3 (i.e. Excel 2003 and earlier).
Hope this helps.
Pete
Spreadsheet uploaded. Apologies for any confusion.
It would help to know whose solution you are trying to implement.
Hiding a couple of extra questions in the sample file is a bit naughty, you're asking for help with formatting results that you don't yet have. Would you have walls decorated before they're built?
I've filled in the formulas that you should have already had and added in conditional formatting for the updated finish dates.
I have a number of cells in a row that lookup the value in a table in another sheet (vlookup). This data relates to the number of days an activity should take to complete based on the scenario. The higher complexity scenario the greater the effort. This however is just a guide and when the actual days taken to complete the task are put in the cell the vlookup number is deleted and replaced with what I refer to as the hard number or manual input number.
What I want to do is highlight via text colour when the number of days for the activity is an estimate vs the hard number. I was hoping there was a formula I could put into the conditional formatting which if the number was based on looking at the vlookup table the font would be red, but if it was a hard number the font would be black.
Jason.b75's solution has fixed my issue around dates so the above is the last piece of the puzzle.
Thanks
In this worksheet, we see the information we want by using conditional formatting, driven by two rules that each contain a formula. The first rule, in column A, formats future birthdays, and the rule in column C formats cells as soon as “Y” is entered, indicating that the birthday greeting has been sent.
b4948c77-0d39-460c-91d0-6423775729e5.jpg
To create the first rule:
1. Select cells A2 through A7. Do this by dragging from A2 to A7.
2. Then, click Home > Conditional Formatting > New Rule.
3. In the New Formatting Rule dialogue box, click Use a formula to determine which cells to format.
4. Under Format values where this formula is true, type the formula: =A2>TODAY()
5. The formula uses the TODAY function to see if the dates in column A are greater than today (in the future). If so, the cells are formatted.
6. Click Format.
7. In the Color box, select Red. In the Font Style box, select Bold
8. Click OK until the dialogue boxes are closed.
9. The formatting is applied to column A.
To create the second rule:
1. Select cells C2 through C7.
2. Repeat steps 2 through 4 above, and enter this formula: =C2="Y"
3. The formula tests to see if the cells in column C contain “Y” (the quotation marks around the Y tell Excel that this is text). If so, the cells are formatted.
4. In the Color box, select White. In the Font Style box, select Bold.
5. Click the Fill tab and select Green.
6. The formatting is applied to column C.
Ranges based on your sample file from post #16, adjust to suit your real file if different.
Select B2:G6 (the range of cells to format).
Excel 2013 (2011 for mac) or newer, apply a conditional formatting rule using =ISFORMULA(B2) to the selected range.
Older versions of excel, create a named range that refers to =(GET.CELL(6,B2)=B2) then use the name of that range as the conditional formatting formula. If using this method, you will need to save your workbook as macro enabled (.xlsm).
Problem now solved.
Thanks to everyone for their input. I have been able to "build a better mousetrap" with the assistance provided.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks