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

Originally Posted by walruseggman

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.

Originally Posted by xladept

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?

Originally Posted by xladept

Hi Steve,

I think this does it:

Please Login or Register  to view this content.

Perfect and awesome! it solved my question marks.

Originally Posted by xladept

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.

Originally Posted by xladept

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?

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?

Originally Posted by xladept

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".

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.

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.

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!