How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard, Software Engineer
Category: Microsoft
Satisfied Customers: 44732
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Question Here...
Richard is online now

Hopefully you can assist me with this question on

This answer was rated:

Hello, Hopefully you can assist me with this question on Excel 2007, which may require VBA. I can confirm I have attached a workbook for demonstration purposes. I have unprotected the workbook and the VBA Project. I can confirm the worksheet named Skills
Matrix within the cell range C4:X48 has a number of cells which are shaded Green (U) with the use of conditional formatting. I require two conditions which will automatically change the Green (U) to Amber (L) or back to Green (U) should the requirements be
met at a different stage of the month. Condition 1: If the efficiency within the range on the KVI Efficiency Tracker worksheet drops below 90%, change U to L [Example cell CL6 is 86.25% on the KVI Efficiency Tracker worksheet, so the corresponding cell in
H6 in the Skills Matrix sheet should turn to L] Condition 2: If the % of errors in row 56 exceeds 1% within the Skills Analysis worksheet, change U to L [Example cell L56 is over 1% under Colleague 10 on the Skills Analysis worksheet, therefore the U’s within
Column L in the Skills Matrix sheet should turn to L] The one caveat is I’m unable to protect the cell range C4:X48 in the Skills Analysis worksheet since the flexibility to change an I or O still needs to be maintained. In respect to Just Answers I have had
a positive experience with a James F Griff. However, if another available member has the capability to address this question, then this will be fine. Any help, would be most appreciated. Rik
Customer: replied 2 years ago.
The follow up point I have attached the workbook as an .xls, since the original format as an .xlsb does not appear to be compatible with the Just Answers site.
Hello there,
I've been working hard to find a Professional to assist you with your question, but sometimes finding the right Professional can take a little longer than expected.
I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.
Please note, I am just a moderator for this category, I can only try and find experts to help, I can't answer the question itself.
Thank you!
Customer: replied 2 years ago.
Hi Adam,Sure, I'm fine to wait. Simply put the Professional would require a strong knowledge of Excel 2007 and probably VBA.
We will continue to look for a Professional to assist you.
Thank you for your patience,
Thank you for your question, my name is ***** ***** I look forward to assisting you.
Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.
Sorry about the delay you have experience.
Two questions please.
1)It will need to be a macro, so will itt be possible you press a button and this is all done? I can then code it to run on a button press and where should the button best be placed for you.
2) For condition to, it is the entire column that changes to L in that example correct?
Thank you
Customer: replied 2 years ago.
Hello,Thank you for your response. In respect to the initial question I would place the macro button on the Skills Matrix worksheet in the left hand corner cell range A1:A2.On the second question yes this is correct, where U’s are present at least. In this instance the quality is based upon the overall error rate and the efficiency is assessed against each individual process.Therefore, if the % of errors exceeds 1%, then all the U's turn L on the corresponding column.
The I's (Training), O's (Authorised by FLM) and blanks remain unchanged.However, if the % of errors is under 1%, then the changes would only be applied based upon the efficiency. Using the example of Colleague 6 process 3, 10 and 11 are below 90%, so the U’s on the three corresponding cells H6, H13 and H14 would turn to L.If the colleague's performance should change say at a later period and equals ‘both’ conditions, month efficiency equal or greater than 90% and the quality is under a 1% error rate then the L's revert back to U's.Hopefully this makes sense. Any further questions, let me know.Rik
Thanks Rik
I am actually based in the UK Timezone and not the US, so its the end of my day now (was just about to sign off).
If it is ok, I can work on this tomorrow for you please?
Customer: replied 2 years ago.
Sure, no problem. Thanks.
thanks and till then
ok, this is now done for you. Can you test it please and let me know the results
Thank you
Customer: replied 2 years ago.
Hello,Thanks for getting back to me. I have just tested the macro. In this instance one part of the macro worked fine.The efficiency under 90% did change to L and Colleague 10 (over 1%) did change from U to L.However, the efficiency over 90% also turned L. So for example cell CN17 on the KVI Efficiency Tracker sheet is currently 144.20%, which corresponds to cell J17 on the Skills Matrix worksheet has turned L. This should remain U.The “I” values under Colleague 12 in Red cells N17 and N19 also turned to an L.In respect to the quality under the column over 1% the intention was only U’s would turn to be L. Using the example of Colleague 10 cells L4, L6:L7, L23, L28, L30:L35. However, all the blank cells turned L along with these cells.Could you look into this when you get a free moment?Thanks, ***** *****Rik
Thanks Rik
Will look into and correct this first thing tomorrow morning for you.
Can you check it here please Rik
Let me know the results please
Customer: replied 2 years ago.
Hello,I have outcome tested the efficiency and quality conditions. I can confirm it is closer albeit a few slight adjustments are required.The conditions tested between the U’s and L’s appeared fine.However, I applied a 91% value under Colleague 2 cell CH35 on the KVI Efficiency Tracker worksheet as a test. In this instance the “O” was changed to a “U”.I applied a 1.5% error rate value under Colleague 9 on the Skills Analysis sheet. In this instance after pressing the button the macro the "O" was overwritten with “L”, I then applied a couple of "I" as a subsequent test and these were also overwritten with "L".One of the key principles is the “O” and “I” is excluded, so in essence these remain unchanged whatever the efficiency or error rate maybe.Any help, would be most appreciated.Rik
ok, So you want O and I to always be left alone now?
That will require a bit of work to recode as it was not initially mentioned as a requirement so please let me know first.
Customer: replied 2 years ago.
Hello,Indeed. I apologise if I did not make it clear in the initial question. I probably clarified it in one of the other messages 10th August 09:55 on the third paragraph.I'm fine to wait a while for completion. Thanks, ***** *****Rik
You can download it from here Rik
Let me know the results please
Customer: replied 2 years ago.
Hello,Thank you for the prompt reply. However, in this instance the “O” and “I” still appear to be overwritten.Using the initial example as a test I changed cell H6 under colleague 6 to an "O" on the Skills Matrix worksheet [corresponding to CL6] which technically should exclude this from a change. However, it turned to “L” related to the efficiency under 90%.I then changed cell I5 to an “I” being the other exclusion which turned back to U after running the macro.In respect to the quality I changed cell N10 to “O” and changed the error rate to 1.2% which turned to “L. However, it is possible condition one was the trigger point.Any help, would be most appreciated.Rik
I see the issue, will work on it now
ok, tested as extensivly as I could.
The issue before was a reference bug which I fixed up.
I went through all the different conditions we have discussed and from what I test it now meets them
You can get it here
Let me know if you have any difficulties please
Richard, Software Engineer
Category: Microsoft
Satisfied Customers: 44732
Experience: Over 15 year experience resolving Microsoft Office Issues
Richard and other Microsoft Specialists are ready to help you
Customer: replied 2 years ago.
Hello,I can confirm I have tested this 7 or 8 times and it work's perfectly, great job. I will leave positive feedback.Thanks.Rik
Customer: replied 2 years ago.
Hello,I just performed another quick check on Monday morning and it turn’s out there was one slight issue.It appears the blanks have not been excluded, in reference to one of my earlier messages.I will provide another example I created three ‘new’ efficiency values in cells where U or L is not present:Colleague 1
CG6: 55%
CG7: 89%Colleague 12
CR4: 91%In this instance the two L’s and one U was populated, rather than remaining unchanged. This is key since there will be some processes where competence is not necessarily required.Could you look into this one if you get the opportunity? I’m guessing this would require an additional <> value in condition one.Thanks.Rik
Customer: replied 2 years ago.
Hello,It's fine. I worked it out, I added an extra <> "" related to the Skills Matrix worksheet under condition 1 on each cell row which appears to have resolved this.Thanks.Rik