Automatic generation of Mark distribution

I need to automate the mark distribution based on each lesson with the below conditions:

N6 to N13 should always be 16 marks and marks should be distributed for that corresponding row which will add up to 16 marks (* Marks per question varies for each question type and i have mentioned it in the column name):

Rule 1: B6:M6 to add up to 16 marks with the following conditions:

if G6 has an input then H6 must be blank and vice versa..

similarly same Rule 1 applies for G7,H7, .....G13,H13.

Rule 2: For column K, from lesson 1 to 4 one random entry and lesson 4 to 8 one random entry.

similarly Rule 2 applies for column L.

The above two rules must satisfy the rule 3.

Rule 3: For column B15:M15 i have already used conditional formatting which turns green when the right number of questions are chosen.

I started to mention in the previous thread that I thought we might be trying to reinvent the wheel as there already exist test generators.
I put 'test generator' and 'test generator free' in a search window and got multiple pages of websites for both.
As to your file, the best that I am able to come up with is to show how the calculations in N6:N14 may be made simpler as well as how you might simplify the conditional formatting rules for B15:M15 for future reference.
As to N6:N14
1. Populate B4:M4 with the number of marks allotted for each type of question.
2. Populate N6:N14 using: =SUMPRODUCT(B$4:M$4,B6:M6)
As to the conditional formatting in B15:M15
1. Populate B3:M4 with the number of questions for each type of question.
2. Use the following for green: =B15=B3
3. Use the following for red: =B15<>B3
4. Both rules are applied to =$B$15:$M$15
Note that rows 3:4 could be easily hidden by changing the font to white.

There might be a possibility that the Analyze utility (Data tab) "Solver" could do what you want. To me some obstacles are the rules for columns G and H as well as K and L. Also not all choices are binary, note that 2 questions have been chosen in several instances. Then again, there are some gifted contributors that know "Solver" much better than I. I suggest that you let this sit through Christmas and if no one responds, send a PM to one of the moderators requesting the thread be moved to the Excel Programming / VBA / Macros forum as that is another possibility. My best suggestion, not to be a broken record, is to search for a pre existing exam generator that might fit your needs and budget.

Sorry not to be of more help. I hope that you have a blessed day.

Thanks for your effort and time. you have simplified the conditional format. I thought of another idea...what if i do the combinations manually and then if i can get the excel to randomly generate those manual combinations ?

Sounds as if it should work, however we would probably need to see the manual combination table and questions together so that we could test.
Let us know if you have any questions.