File Insert Page Layout FormulasData Review View Developer Add-ins Analytic Solver Platform XLMiner Platform Solver Home Tell me what you want to do Cut Copy Fonmat Painter AutoSum 9- Wrap Text General 1 Clear, Paste Merge & Center. $. % , 2 Sort & Find & Filter Select Conditional Fonmat as Cell Insert Delete Format B 1 u . ormatting TableStyles Clipboard Font Alignment Number Cells Editing G23 2 a Sub HLarge(): Create a macro that will highlight quantity values in the All Orders table which are greater than average. In Excel, name the Quantity label "QStart - In VBA, insert a new Module and create a new macro called Hlarge Dynamically name the range from one cell below "QStart" to the last cell down from "QStart" as "QVals - Clear any previous conditional formatting from the "QVals" range (record a macro to check they syntax). -- Add an Above Average conditional formatting setting to the "QVals" range (record a macro to check the syntax). Set the Interior.Color property of this formatting condition to vbGreen 9 b Sub ClearH(): Create another macro that will clear any format colors from the Quantity column. -- Dynamically name the range from one cell below "QStart" to the last cell down from "QStart" as "QVals Make sure your macro clears any new highlights added to the dynamic "QVals" range. 12 Sub DoReport(): create a macro that will calculate the Total Revenue in the aggregate report table. -- Assume the product table and report table are fixed. Name these tables in Excel as "PTable" and "Report" (do not include labels). Dynamically name the range from one cell below "QStart" to the last cell down from "QStart" as "QVals - Name the range offset once to the right of "QVals" as "Rev". Name the range offset once to the left of "QVals" as "PVals - Use VBA to enter a formula in the "Rev" column which looks up the corresponding "PVals" product name from the "PTable" and returns the unit price value and then multiplies it by the corresponding "QVals" quantity value. - Use VBA to calculate the total revenue value for each row in the report table; this should be the result of a function which sums the "Rev" values if the corresponding product is in "PVals" - Clear the contents of the temporary "Rev" range 20 d Sub ClearReport(): Create a macro to clear the Total Revenue values from the Order Report table File Insert Page Layout FormulasData Review View Developer Add-ins Analytic Solver Platform XLMiner Platform Solver Home Tell me what you want to do Cut Copy Fonmat Painter AutoSum 9- Wrap Text General 1 Clear, Paste Merge & Center. $. % , 2 Sort & Find & Filter Select Conditional Fonmat as Cell Insert Delete Format B 1 u . ormatting TableStyles Clipboard Font Alignment Number Cells Editing G23 2 a Sub HLarge(): Create a macro that will highlight quantity values in the All Orders table which are greater than average. In Excel, name the Quantity label "QStart - In VBA, insert a new Module and create a new macro called Hlarge Dynamically name the range from one cell below "QStart" to the last cell down from "QStart" as "QVals - Clear any previous conditional formatting from the "QVals" range (record a macro to check they syntax). -- Add an Above Average conditional formatting setting to the "QVals" range (record a macro to check the syntax). Set the Interior.Color property of this formatting condition to vbGreen 9 b Sub ClearH(): Create another macro that will clear any format colors from the Quantity column. -- Dynamically name the range from one cell below "QStart" to the last cell down from "QStart" as "QVals Make sure your macro clears any new highlights added to the dynamic "QVals" range. 12 Sub DoReport(): create a macro that will calculate the Total Revenue in the aggregate report table. -- Assume the product table and report table are fixed. Name these tables in Excel as "PTable" and "Report" (do not include labels). Dynamically name the range from one cell below "QStart" to the last cell down from "QStart" as "QVals - Name the range offset once to the right of "QVals" as "Rev". Name the range offset once to the left of "QVals" as "PVals - Use VBA to enter a formula in the "Rev" column which looks up the corresponding "PVals" product name from the "PTable" and returns the unit price value and then multiplies it by the corresponding "QVals" quantity value. - Use VBA to calculate the total revenue value for each row in the report table; this should be the result of a function which sums the "Rev" values if the corresponding product is in "PVals" - Clear the contents of the temporary "Rev" range 20 d Sub ClearReport(): Create a macro to clear the Total Revenue values from the Order Report table