How do I populate value into multiple cells and color code based on options?

Screenshot of Excel Project:

I am developing a spreadsheet to perform the following:
-Populate the # value into the corresponding column options and color code it based on option 1 or option 2.
-Automatically move the # value to the correct columns if options change.
-Use SumByColor to calculate the total per option under each Option Type.
-Only the cells populated with data will be color coded by their option number.

Using an IF formula, I move the numbers automatically to the right column if the option changes.

I cannot figure out how to color code it so I can use the SumByColor VBA macro.

For Cell E5: (Ham being first option will be Green)

=IF($B5="Ham", $D5, IF($C5="Ham", $D5, ""))

For Cell F5: (If either Option 1 or 2 is changed to Turkey, then the value of D5 will be moved to F5 and color code to match Option 1 or Option 2)

=IF($B5="Turkey", $D5, IF($C5="Turkey", $D5, ""))

For Cell G5: (Duck is the second option and will be Yellow)

=IF($B5="Duck", $D5, IF($C5="Duck", $D5, ""))

I tried Conditional Formatting and VBA macros that SMEs have provided for similar problems, but neither have the desired results.

A more dynamic formula (starting in cell E5):

=IF($B5=E$1,$D5,IF($C5=E$1,$D5,""))

Drag down and to the right.

Now to colour code the cells, you need 2 conditional formatting rules:

• Select the entire range of data (E5:I9), with cell E5 active
• Add a new rule, using formulas
• Rule 1: =$B5=E$1;
• Change the cell layout to desired result (change background to green);
• Rule 2: =$C5=E$1;
• Change the cell layout to desired result (change background to yellow);

To sum up your choices, using formula:

In E2: =SUMIFS(E5:E9,$B$5:$B$9,E$1), drag right;

In E3: =SUMIFS(E5:E9,$C$5:$C$9,E$1), drag right;