excel - protect column & ifstatements

hi experts, could you please help with the attached excel workbook, id like to;
- protect a column with a pwd protection to edit the cells in this column
- add ifstatement to the worksheet so that if it meets a criteria certain cells are colored according to the criteria
C--Users-frank.sasso-zFrank-Folder-.xlsx
Frank .SBuilding EstimatorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
You are asking for two entirely unrelated things.

1. Select the column, bring up the Format Cells dialog box again (Ctrl+Shift+F), select the Locked box and then click OK, on the Review tab, in the Changes group, click Protect Sheet.
2. Use conditional formatting: http://www.excel-easy.com/data-analysis/conditional-formatting.html
1
Frank .SBuilding EstimatorAuthor Commented:
hi macroshadow, ive had a look at the conditional formatting link you have posted in your comment but i cant see if in a range of cells you can apply multiple conditions for example if the date = or is 1 day less than what is shown in the cell then its hilited a certain color?
0
MacroShadowCommented:
Yes, it is possible using a formula.
The formula must return true for the formatting to be applied.
https://exceljet.net/conditional-formatting-with-formulas
1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Frank .SBuilding EstimatorAuthor Commented:
hi experts, could you tell me how i apply more than 1 condition to a range of cells through conditional formatting, so for example if cells a1:a40 = "to check" then hilite cell fill color as red, or if = "checked" then cell fill color to be green or if cell = any other value show as yellow fill color
0
MacroShadowCommented:
If you want different formatting of the same cell depending on its contents, use multiple rules.
0
Frank .SBuilding EstimatorAuthor Commented:
hi macroshadow, im using excel 2007 and i cant work out how to use multiple rules, could you assist?
0
Frank .SBuilding EstimatorAuthor Commented:
hi, ive added 2 conditional formats into my wksht in column 'e' but its not working, could you please check why?
protect---ifstatements_2.xlsx
0
MacroShadowCommented:
I don't understand your requirements. Please fill in the blanks:

If cell in column e = f1 then fill cell(s) _____________ red
If cell in column e = f1 -1 then fill cell(s) _____________ yellow
If cells in column h = "to check" then fill cell(s) _____________ red
If cells in column h = "checked" then  fill cell(s) _____________ green

Also note that both column e and f1 contain numbers not dates.
0
Frank .SBuilding EstimatorAuthor Commented:
hi macroshadow,

If cell in column e = f1 then fill cell(s) _____________ red
If cell in column e = f1 -1 then fill cell(s) _____________ yellow
If cells in column h = "to check" then fill cell(s) _____________ red
If cells in column h = "checked" then  fill cell(s) _____________ green

yes these are the requirements above and if you open my conditional formatting/ manage rules the conditions dont work, there is obviously something missing, can you pick up the error/s?
I changed column e & f1 to number to see if my conditional formatting would work but still does not.
0
MacroShadowCommented:
What cells should have the formatting applied to?
0
Frank .SBuilding EstimatorAuthor Commented:
the range of cells the formatting applies to is e3:e7 as shown in the screenshot below.
screenshot 1
0
MacroShadowCommented:
0
Frank .SBuilding EstimatorAuthor Commented:
there seems to be a misunderstanding, the formatting still applies to all cells in column 'e' but the conditions are 3;
1. if the cell value in col 'e' is = to or greater than the value in cell f1 then the background fill color is to be red
2. if the cell value in col 'e' is = to the value in cell f1 minus 1, then the background fill color is to be yellow
3. if the cell value in col 'e' doesnt meet condition 1 or 2 above then the background color is remain as normal, no formatting.
0
MacroShadowCommented:
That is not what you wrote above, you mentioned checked and to check.
Anyway, is this what you want?
protect---ifstatements_2.xlsx
0
Frank .SBuilding EstimatorAuthor Commented:
hi macroshadow, yes this is what i want but with cell values from numbers to dates. Ive attached the same workbook and the only change is the date values instead of numbers but it looks like all the conditional formats have been lost? i would have thought changing from numbers to dates wouldnt have changed the conditional formats?
protect---ifstatements_3.xlsx
0
MacroShadowCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank .SBuilding EstimatorAuthor Commented:
hi macroshadow, ive checked your last worksheet but the only thing that works are the dates in the table if = or > F1 then col e changes to red, if col e value = the date before F1 then shows in yellow.
col h doesnt work at all.
0
MacroShadowCommented:
I hope this is what you want.
protect---ifstatements_3.xlsx
0
MacroShadowCommented:
No answer has been accepted by the OP, but this post does exactly what the OP requested.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
if statements

From novice to tech pro — start learning today.