+ Reply to Thread
Results 1 to 22 of 22

Auto Match and apply condition formatting at the same time in excel

  1. #1
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Exclamation Auto Match and apply condition formatting at the same time in excel

    Dear all,

    I have a file which need macro to assist. I am quite new to it so I need you guys to help me urgently.

    Inside my file have 6 sheets name as "MAIN", "DD","CD" , "DAE" & "UPDATE". "UPDATE" sheet will be updated every now and then.

    Whenever "UPDATE" sheet updated, data in "MAIN", "DD" & "CD" sheet will be highlighted accordingly.
    - if task accepted in "UPDATE" sheet but not completed, it will be highlighted with amber.
    - if task accepted in "UPDATE" sheet and completed, it will be highlighted with blue.
    - if task NOT accepted in "UPDATE" sheet but completed, it will be highlighted with blue.
    - if task NOT accepted in "UPDATE" sheet and not completed, it will be highlighted with amber.
    - IF TASK DOES NOT HAVE ANY STATUS IN ACCEPTANCE BUT COMPLETED, IT WILL BE HIGHLIGHTED WITH BLUE.
    - if there is nothing enter in column C under acceptance, it will be highlighted with amber.

    Do note that, the macro need to match data in column A & B before proceed with highlight. If data not match, it will continue check the next row and follow by sheet. Apologise first if my language confuse you guys, maybe the attachment will help to clarify.
    Attached Files Attached Files
    Last edited by steveliew; 04-04-2016 at 10:08 AM. Reason: title not accurate

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Newbie need help in macro creating

    FYI, Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    This code belongs on the UPDATE sheet object, and will run when anything in the CARRY OUT? column is added/changed, assuming the other three columns are not blank for that row.

    Please Login or Register  to view this content.
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Newbie need help in macro creating

    Here's another and welcome to the Forum!

    Please Login or Register  to view this content.
    Last edited by xladept; 03-25-2016 at 02:29 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Newbie need help in macro creating

    Quote Originally Posted by walruseggman View Post
    FYI, Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    This code belongs on the UPDATE sheet object, and will run when anything in the CARRY OUT? column is added/changed, assuming the other three columns are not blank for that row.

    Please Login or Register  to view this content.
    Hi Title change accordingly.
    However, the macro seems having issue. System keep on prompting me to create a macro name upon run.

  5. #5
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Newbie need help in macro creating

    Quote Originally Posted by xladept View Post
    Here's another and welcome to the Forum!

    Please Login or Register  to view this content.
    Macro run perfectly for Sheet DD and CD but not for MAIN. Possible to guide me?

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Auto Match and apply condition formatting at the same time in excel

    Hi Steve,

    I think this does it:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Auto Match and apply condition formatting at the same time in excel

    System keep on prompting me to create a macro name upon run.
    This Sub will run automatically any time you make any change to Column D on the UPDATE sheet. You do not need to try to run it yourself.

  8. #8
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Auto Match and apply condition formatting at the same time in excel

    Quote Originally Posted by xladept View Post
    Hi Steve,

    I think this does it:

    Please Login or Register  to view this content.
    Perfect and awesome! it solved my question marks.

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Auto Match and apply condition formatting at the same time in excel

    Hi Steve,

    Thanks for the rep!

    This routine depends on the Areas being the way you've shown - if that's not the case, we'll need to rewrite:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Auto Match and apply condition formatting at the same time in excel

    Quote Originally Posted by xladept View Post
    Hi Steve,

    Thanks for the rep!

    This routine depends on the Areas being the way you've shown - if that's not the case, we'll need to rewrite:

    'The areas are one or two letters, if two then they have their own sheet
    If Len(A) = 1 Then Sheets("MAIN").Select Else Sheets(A).Select
    'find the row that matches both the A and B entries
    s = 2: Do Until Cells(s, 1) = A And Cells(s, 2) = B
    'Guard against overflow
    If Cells(s, 1) = "" Then GoTo GetNext
    s = s + 1: Loop: Cells(s, 2).Interior.ColorIndex = CI
    'Color the B entry and process the next update
    GetNext: Next r
    End Sub[/CODE]
    Hi Xladept, I saw that you make reference to the areas with one or two letters as per the code quoted. What if the condition change and you have to make reference to more letter and it can be the same throughout the Main, DD and CD sheet?

    For better understanding, I have redo the attachment file and hope you able to assist. Thanks in advance.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Auto Match and apply condition formatting at the same time in excel

    Hi Steve,

    Try this:

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Auto Match and apply condition formatting at the same time in excel

    Quote Originally Posted by xladept View Post
    Hi Steve,

    Try this:

    Please Login or Register  to view this content.
    Hi Xladept, it seems like not that stable yet for this macro. In the DD Sheet, item 5 highlighted in Amber even though it is not on Update sheet. Any idea why?

  13. #13
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Auto Match and apply condition formatting at the same time in excel

    Hi Xladept, if I need to add one more sheet name "DAE" & the auto match need to check up on this sheet as well? which code do I need to change?

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Auto Match and apply condition formatting at the same time in excel

    Hi Steve,

    Please Login or Register  to view this content.
    *Item 5 in DD sheet does not get highlighted on my sample?
    Last edited by xladept; 03-31-2016 at 01:07 PM.

  15. #15
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Auto Match and apply condition formatting at the same time in excel

    Quote Originally Posted by xladept View Post
    Hi Steve,

    Please Login or Register  to view this content.
    *Item 5 in DD sheet does not get highlighted on my sample?
    This macro is meant for additional sheet? if not wrong, the different between this and original macro is at Len (Tp) <4.
    I tried to add in but it prompt "Running Error 9" and "Subscript out of range".

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Auto Match and apply condition formatting at the same time in excel

    Have you got a different sample with different sheet names?

  17. #17
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Auto Match and apply condition formatting at the same time in excel

    I have attached the file, you can find it in the first post. By the way, I have added in one more condition and it was highlighted in red.
    Over here, i would like to say million of thanks to you as you are very patient in guiding me along the way. Really appreciate for it.

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Auto Match and apply condition formatting at the same time in excel

    Hi Steve,

    There is no sheet named WW and no data in update for DAE. - Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 04-02-2016 at 03:35 PM.

  19. #19
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Auto Match and apply condition formatting at the same time in excel

    Hi Xladept,

    There is Sheet name DAE but the type of work is WW. I tried the code, if I change the type of work to DAE which same as sheet name. It's work!
    But, the type of work (WW) will not be the same as sheet name (DAE) in this case.

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Auto Match and apply condition formatting at the same time in excel

    So, what do you want to do?

  21. #21
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2013
    Posts
    16

    Re: Auto Match and apply condition formatting at the same time in excel

    Hi Xladept,

    Great! I change the sheet name to WW instead of DAE and everything work fine now. Thanks for your patience in guiding me through this. You are the best!

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,380

    Re: Auto Match and apply condition formatting at the same time in excel

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hellos, another newbie here. An old newbie 31 years just started to use VBA!
    By vba_newbie83 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-31-2014, 03:37 AM
  2. [SOLVED] Creating a new excel workbook - newbie help!
    By sar2004 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-31-2014, 12:55 PM
  3. Newbie in Macro
    By Carbonlink in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-20-2014, 07:45 PM
  4. Replies: 8
    Last Post: 07-05-2013, 11:23 PM
  5. Replies: 2
    Last Post: 08-28-2012, 05:55 AM
  6. Replies: 4
    Last Post: 06-13-2012, 01:34 AM
  7. Newbie - Help with a macro
    By Mike Basden in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-08-2006, 08:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1