VBA Pseudo Conditional Formatting With User Log In

A while ago a User asked a question with somewhat complicated conditional formatting that also required an existing User Id. I can't find the original question, but I was interested in the question, and had some time for a solution. The question was something like:

I am looking to conditionally format column B. In column C, there is a ‘Date to be completed’.

If there is no date in column C, column B should not be formatted.
If column C has a date, and today’s date is within 7 days plus or minus, column B cell should be yellow.
If today’s date greater than 7 days from the column C date, column B cell should be red.
Once a date is entered in column E – completed date field – I would like whatever condition that may have been applied above to persist.

In column E, I have a completed date field. Once the completed date is entered, column A cell should be green.

In column H, ‘Completed By’ column – is there any way to auto populate the user that inputs the completed date in column E? No idea if that is a possibility.

-------------------
I translated the above requirements into the following that was easier for me to follow:
a. Add 'Completed By' Name in Column 'H' when date is entered in Column 'E'.

b. Turn Column 'A' green when Column 'E' has a date.


c. Column 'B' no Color when, Column 'C' is BLANK.
d. Column 'B' Yellow when, Column 'C' is <= Today +/- 7 days.
e. Column 'B' red when, Column 'C' is > Today +/- 7 days.
f. Column 'B' DOES NOT CHANGE COLOR when there is a Date in Column 'E'

-----------------
Since I am not very good at Conditional formatting (maybe that is why I don't like it), I solved the problem (see attached file and code below) using:
a. Small UserForm to select from a list of pre-existing User Ids.
b. Worksheet_Change event handlers to satisfy the other requirements

In the ThisWorkbook Code module:

Please Login or Register  to view this content.

In the Sheet1 Code module:

Please Login or Register  to view this content.

In the UserFormLogin Code Module:

Please Login or Register  to view this content.

In the ModPseudoConditionalFormattingOrdinary Code module:

Please Login or Register  to view this content.

In the ModUserFormLoginOrdinary Code module:

Please Login or Register  to view this content.

Lewis

That is pretty awesome, Lewis. Thank you.

I was trying to make 1 tweak.....regarding: If today’s date is greater than 7 days from the column C date, column B cell should be red.

The cell should only go red if I am 8 days or more 'past due'. So if column C had a 'date to be completed' date of 1/13, come tomorrow, that would go from yellow to red. The ABS +/- for the yellow condition is perfect...for red, it should be +8 or more - red - and currently it is doing +8 and -8 red.

Didn't have success making the tweak! The user login form is pretty cool - so the only maintenance there is add the users to the code, and re-alphabetizing as new users were added if that was my preference. Thank you again - really appreciate it.

Hi Ryan,

Thanks for the rep points. Try the attached file, changes I made are in red below:

Please Login or Register  to view this content.

Worked perfectly. Thank you again Lewis. Greatly appreciated.

Hi Lewis, follow-up question for you.

This section of code you have in the 'ThisWorkbook' module:

'Verify the Pseudo Conditional Formatting on the Data Sheet
Application.EnableEvents = False
Call PseudoConditionalFormattingInDataEntryRange(Worksheets(sGblDataEntrySheetNAME).Range(sGblDataEntryRANGE))
Application.EnableEvents = True


I am working on some other macros - and in testing something - I did remove the entry above and all of your conditional formatting appears to function as designed. Since the functionality appears to still work, curious as to what the code above in that specific module is doing. Thanks!

It is a defensive programming technique.

If for some reason, either intentionally (or due to error) events are disabled, my code will not operate because it relies on Excel events to be enabled. The Workbook_Open() event in the ThisWorkbook module (that contains the above code) is automatically run when the workbook is opened and Excel Events are enabled. This will take a little time, but will guarantee that the contents of your sheet are correct. In a perfect world, the code is not necessary.

I turn events off and on while the code is running, because the code modifies cell values (and could possibly call itself over and over again forever). Sort of like a dog chasing it's tail.

Lewis