Cornstarch
New Member
- Joined
- Jan 17, 2018
- Messages
- 7
Hi guys, hope this message finds you well, and that you can have the kindness to spare a moment showing me the way to crack this problem.
I spent long time trying conditionnal formatting, and data validation to find a solution but it seems VBA is my only salvation...
Idealy the result after running the macro would be like that:
The cells blocking & coloring should consider columns I, H, and G content.
Example:
In raw 6, If type is "Base" in column I then highlight in red and block input J6 to O6, then highlight in Orange P6 to R6 not blocking but allowing only 20% adjustment compared to the already existing value. Binding period being 9 months, 6 have to be blocked, " next to follow condition stated in H.
The idea would be to repeat the same logic for others considering the conditions stated in columns G and H.
Case raw 15 and 16 being a bit special as the orange period has 2 subconditions. -10% in 4th quarter only, while in the Q5 and Q6 +/-10% is allowed.
Good to know, users will insert rows (not columns) in their review process and pick a type in column I that can be "Base" or other. How can the macro catch up on this raw addition? The reactivation of the VBA might impact previous deviations and let users make more than the allowed deviation?
Thanks a lot!
Julien
I spent long time trying conditionnal formatting, and data validation to find a solution but it seems VBA is my only salvation...
Idealy the result after running the macro would be like that:
The cells blocking & coloring should consider columns I, H, and G content.
Example:
In raw 6, If type is "Base" in column I then highlight in red and block input J6 to O6, then highlight in Orange P6 to R6 not blocking but allowing only 20% adjustment compared to the already existing value. Binding period being 9 months, 6 have to be blocked, " next to follow condition stated in H.
The idea would be to repeat the same logic for others considering the conditions stated in columns G and H.
Case raw 15 and 16 being a bit special as the orange period has 2 subconditions. -10% in 4th quarter only, while in the Q5 and Q6 +/-10% is allowed.
Good to know, users will insert rows (not columns) in their review process and pick a type in column I that can be "Base" or other. How can the macro catch up on this raw addition? The reactivation of the VBA might impact previous deviations and let users make more than the allowed deviation?
Thanks a lot!
Julien
