excelvba

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

Screenshot of Excel Project: Screenshot of Excel Project

I am developing a spreadsheet to perform the following functions: -Populate the # value into the corresponding column options and color code it based on whether it's option 1 or option 2. -Automatically move the # value to the correct columns if a person's 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

My Excel-fu is at a White-belt level and I have googled as much as I can to try and figure this out. Using the IF formula statement, I was able to at least have the numbers move automatically to the right column if the person changes their option, but 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 have used Google and AI to search for a solution. Everything keeps pointing towards VBA macro or Conditional Formatting.

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

Solution

  • 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:

    Result

    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;

    Sums