Changing Text Changes Color and Counting Based On Color

Changing Text Changes Color and Counting Based On Color
I am a novice and still learning VBA.

I am herewith enclosing excel sheet containing data.
from Column A to M. except that D column is left blank for later entering data
into that column. I need VBA code and the following are my requirements.

1. If I Use the Keyboard shortcut Ctrl + Y it should insert in the current cell in Column A "Done" and Bold it and then it has to fill in the Yellow as Background color; also while it does this at the same time, I want from the Column B to Column I to be filled with Background color Yellow.

In the same way above for the Keyboard shortcut Ctrl + G it should insert in the current cell in Column A "My Desk" and Bold it and then it has to fill in the Green as Background color; also while it does this at the same time, I want from the Column B to Column I to be filled with Background color Green.
2. In a similar way as above, I need for the other contents in Column A (which you can view in the excel sheet enclosed) there are other status named viz… Print Ready, Priority Sent, Searched with each referenced to the colors that is filled in the excel sheet and with each being given a Keyboard shortcut assigned like above (and I leave those keyboard shortcuts to your choice).

3. The complex part begins. I have already data collected in Column J beginning at J13 named as Client and below that are the names of clients that are multiple/occurrences which are extracted from Column B named as Client and in the next Column K from K14 the multiple/occurrences counts of the clients are calculated already and is displayed (please see my excel sheet).

In Column L beginning at L10 and below is a list of Date Sent that are
multiple/occurences extracted from Column E named as Date Sent and in the next
Column M from M11 the multiple/occurences counts of Date Sent are calculated already and is displayed (Please see my excel sheet).

Now my requirement is that I want to use the functions IF and AND Then Else
etc....in VBA to do the following.

1. IF Column A = "Done" AND Column B = yellow background color AND Column B
MATCHES with Column J14 Then Column O14 minus 1 from Column K14.
For example;
A2 shows Done. So by using the coloring method which I requested above in question 1, the Column B to I will change to yellow. As Column B's background color is yellow now and the contents of B2 is Aaron Bee and if these two criterias match with J14 which has Aaron Bee, Then this formula should minus 1 transaction displayed in O14 and do the same for the other clients upto the last row wherever it ends as the data will vary sometimes.
4. Can I have the above rule applied for the Date Sent counts minus 1 displayed at Column
P11 onwards until last row wherever it ends as the data will vary sometimes.
I do not know if my explanation is clear to you, but I have tried my best to explain. I appreciate any queries though from you; please excuse me if I have gotten things wrong. Please raise any queries which I am unaware of. I want this to be done only in VBA and conditional formatting is a tedious job to copy and paste. Thank you in advance.

Solution with userform

call userform by Ctrl Shift Y or by dubbelclick


Kind regards
Leo

Leo Thank you so much..... you have created this form beautifully than I expected using forms........and you have solved my first 2 questions. Thank you once again.........hope you are working out on my other questions..thanks a tonne in advance.... you have saved me all the manual work I used to do.... God bless you....thank you thank you....i am so elated by what you have given me.......

Thank you Leo... i forgot to mention... that you have given me two options as well double click as well as Ctrl Shift Y .....this is excellentttttttttttttttttt ...... I just want to hug you brother.....God bless you.....

Just one problem I have brother Leo...when I tried to copy this code to the official workbook which already have macros, but they are not working out in those sheets. How do I go about this....?? I am not so good in VBA commands though....I tried copying from your sheet1 the private code as well created a user form and copied your commands there.....and its not working there..it just pops up the user form though and when i try to click on them nothing seems to happen...and Ctrl Shift Y too doesnt work in this workbook I copied to.....please help me out on this..

Part of userform is hidden, you can see complete in this


Kind regards
Leo

ok...will try this now..thank you bro..

Yes, its working bro......just let me know that those 2 blank boxes one fills the black color ..and what does the 1st blank box do??? coz i tried and could not make of it......

1 textbox for the color, 1 textbox for the value for column A

Kind regards
Leo

Fine thanks a tonne for explaining to me with so much patience... really appreciate you.....thanks a tonne..... ... well let me know once you have worked on my other queries as you have solve one part of my query......greatly appreciable...of all your efforts and time you have spent on this......you guys really rockstars of this site....))

in file provided by me all queries are done


Kind regards
Leo

Oh is it..... well....just check......today on live data...thanks once again.....bro...leo

Bro leo....the Client part works with color being yellow it reduces 1 from each...client......but the Date Sent part (M11 and M12 with total at M13) remains static and does not reduce with -1 for each yellow background already filled in Column E (Date Sent)....my query 4 is not working....can you kindly check please....and let me know... thank you...

hi....Bro leo....the Client part works with color being yellow it reduces 1 from each...client.....at Column K, and can I have this deducted data in the Column O....instead of K......how....help me on this please and also .....the Date Sent part (M11 and M12 with total at M13) remains static and does not reduce with -1 for each yellow background already filled in Column E (Date Sent)....my query 4 is not working for this....can you kindly check please....and let me know... thank you...

hi...bro Lee...still working on the Date Sent is it....

For M11

Please Login or Register  to view this content.

For M12

Please Login or Register  to view this content.

Kind regards
Leo

Or

for M11

Please Login or Register  to view this content.

for M12

Please Login or Register  to view this content.

Cheers
Leo

thanks bro Leo..since I dont know where to place this, can you please copy this to the excel sheet you created and send it to me....kind request please....thank you once again for all the trouble you took to bring this out excellently.....i really commend your work... cause you made life so easy for the daily routine....

to place in Cell M11 and Cell M12


cheers
Leo

hi....bro...thank you so much....well that works fine for the two rows.....if sometimes this Date Sent data will be more and it will extend beyond this two....how i am supposed to include this in macro i dont know....can u include this part in your final sheet and send it over to me.....thanks for all the trouble you have taken......kind of you.......

hi....bro..leo....thank you so much....well i figured it out somehow how to place them... but I have one last request....for you to make one change.....for the user form you had created, can I have key board shortcuts for My Desk as Alt M. Alt D for Done. Alt P for Priority Sent. Alt S for Searched. Alt N for Print Ready as the users are feeling keyboard shortcuts are faster than using mouse. Just can you make this final change in that sheet with all the rest and send me the final one. Thanks ...hope i am not troubling you a lot......I know you will be able to nail this down....thanks in advance.. God bless your work...

hi....bro..leo....any updates done.....thank you so much....well i figured it out somehow how to place them... but I have one last request....for you to make one change.....for the user form you had created, can I have key board shortcuts for My Desk as Alt M. Alt D for Done. Alt P for Priority Sent. Alt S for Searched. Alt N for Print Ready as the users are feeling keyboard shortcuts are faster than using mouse. Just can you make this final change in that sheet with all the rest and send me the final one. Thanks ...hope i am not troubling you a lot......I know you will be able to nail this down....thanks in advance.. God bless your work...

With DubbelClick in wright Column, you change status
and formulas for the client and dates list

shortcuts is not my peace of cake

Kind regards
Leo

Wow...Bro Leo...thank you this is enough I think...and you have nailed it ............God bless you..... thank you for all the trouble you had taken.
You are superfastttttttt......in cracking.......Next time, any queries I have.....i will surely come in to this site....thanks for your kind help...no words to
express my happiness and gratitude to you and the forum....God bless you all.....I will close this as solved...today....