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 a VBA to person the following requirements.
1. While I am in any row in Column A (named status) and If I Use the Keyboard
shortcut Ctrl + Y it should insert the current row "Done" and Bold it and then
it has to fill in the Color Yellow as Background color; also at the same time,
the current row where "Done" is I want the Column B and upto I Column to be
filled with Yellow color.
2. While I am in any row in Column A (named status) and If I Use the Keyboard
shortcut Ctrl + G it should insert the current row "My Desk" and Bold it and
then it has to fill in the Color Green as Background color; also at the same
time, the current row where "My Desk" is I want the Column B and upto I Column
to be filled with Green color.
3. Next, in the same way, I need for the other contents in Column A that is for
Print Ready, Priority Sent, Searched with each referenced to the colors that are
mentioned in the excel sheet with each a Keyboard shortcut assigned like the 1
and 2 above.
4. The complex part begins.
I have already data in Column J beiginning at J13 named as Client and below that
the names of clients that are multiple/occurrences are extracted from Column B
named as Client and in the next Column K from K14 the counts of the clients that
is multiple/occurrences are calculated already and is available.
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 counts of Date Sent that is multiple/Occurences are
calculated already and is available.
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 using the Coloring method Column B to I will change to yellow
as per my request for a VBA 1st requirement above. So Column B's background
color is yellow and the contents of B2 is Aaron Bee and if the two criterias
match with J14 Then this formula to reduce one transaction is displayed in O14.
2. In the same way I want it for the other clients and upto the last row
wherever it ends as the data will vary sometimes.
3. Can i have this same way for the Date Sent counts minus 1 displayed at Column
P11 onwards until last row wherever it ends as the data will vary sometimes
If there is any queries in my explanation though I dont know if I have explained
in an understanding way to you, please excuse me. Please raise any queries
which I am unaware of. I want this to be Done only in VBA and not using
conditional formatting.
Thank you in advance.
Bookmarks