Home

How can I auto fill from a "Data" worksheet to a "Report' worksheet based on Cell Interiour color?

%3CLINGO-SUB%20id%3D%22lingo-sub-450700%22%20slang%3D%22en-US%22%3EHow%20can%20I%20auto%20fill%20from%20a%20%22Data%22%20worksheet%20to%20a%20%22Report'%20worksheet%20based%20on%20Cell%20Interiour%20color%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-450700%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20see%20the%20attached%20work%20book.%26nbsp%3B%20I%20use%20this%20workbook%20for%20doing%20reviews%20of%20clients%20databases.%26nbsp%3B%20There%20is%20a%20sheet%20for%20the%20data%20and%20a%20sheet%20for%20the%20report.%26nbsp%3B%20I%20assign%20a%20color%20code%20to%20each%20item%20that%20is%20not%20in%20compliance%20and%20I%20add%20a%20solution%20for%20each%20out%20of%20compliance%20item.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Report%20sheet%20is%20broken%20up%20by%20severity%2C%20so%20there%20are%20Red%2C%20Orange%20and%20Yellow%20sections%20where%20I%20copy%20and%20paste%20the%20non-compliance%20items%20from%20the%20Data%20sheet.%26nbsp%3B%20I%20would%20like%20to%20automate%20this%20task%20so%20that%20the%20Red%2C%20Orange%20and%20Yellow%20sections%20auto%20populate%20according%20the%20cell%20with%20the%20Severity%20color%20code%20from%20the%20Data%20sheet.%26nbsp%3B%20The%20number%20of%20rows%20to%20evaluate%20remains%20constant%20but%20the%20number%20of%20Red%2C%20Or%2C%20Yellow%20codes%20varies%20from%20report%20to%20report.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20copied%20in%20some%20code%20to%20allow%20to%20evaluate%20the%20InteriorColor%20and%20it%20works%20but%20is%20there%20a%20way%20to%20evaluate%20a%20fixed%20range%20of%20cells%20and%20return%20a%20variable%20response%20and%20sort%20according%20the%20cell%20color%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3CP%3ERob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-450700%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-452490%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20auto%20fill%20from%20a%20%22Data%22%20worksheet%20to%20a%20%22Report'%20worksheet%20based%20on%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-452490%22%20slang%3D%22en-US%22%3EA%20couple%20of%20suggestions.%3CBR%20%2F%3E1.%20You%20would%20make%20your%20(and%20Excel's)%20life%20easier%20if%20you%20would%20use%20values%20in%20cells%20rather%20than%20a%20color.%20To%20visualise%20that%20on%20the%20sheet%2C%20you%20can%20always%20conditionally%20format%20those%20cells%20to%20color%20them%20based%20on%20their%20value.%20%3CBR%20%2F%3E2.%20Don't%20leave%20any%20empty%20rows%20in%20your%20data%2C%20sorting%20and%20filtering%20is%20easier%20of%20the%20data%20has%20no%20blank%20rows%20in%20between.%3CBR%20%2F%3E3.%20Perhaps%20you%20can%20use%20a%20pivot%20table%20to%20summarize%20the%20data%20(after%20taking%20head%20of%20my%20first%20remark)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-708163%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20auto%20fill%20from%20a%20%22Data%22%20worksheet%20to%20a%20%22Report'%20worksheet%20based%20on%20Cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-708163%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%20I%20will%20give%20it%20try%20when%20I%20can.%2C%20Thank%20you.%3C%2FLINGO-BODY%3E
PdM_Rob
New Contributor

Please see the attached work book.  I use this workbook for doing reviews of clients databases.  There is a sheet for the data and a sheet for the report.  I assign a color code to each item that is not in compliance and I add a solution for each out of compliance item. 

 

The Report sheet is broken up by severity, so there are Red, Orange and Yellow sections where I copy and paste the non-compliance items from the Data sheet.  I would like to automate this task so that the Red, Orange and Yellow sections auto populate according the cell with the Severity color code from the Data sheet.  The number of rows to evaluate remains constant but the number of Red, Or, Yellow codes varies from report to report. 

 

I have copied in some code to allow to evaluate the InteriorColor and it works but is there a way to evaluate a fixed range of cells and return a variable response and sort according the cell color?

 

Thank you in advance.

Rob

2 Replies
A couple of suggestions.
1. You would make your (and Excel's) life easier if you would use values in cells rather than a color. To visualise that on the sheet, you can always conditionally format those cells to color them based on their value.
2. Don't leave any empty rows in your data, sorting and filtering is easier of the data has no blank rows in between.
3. Perhaps you can use a pivot table to summarize the data (after taking head of my first remark)
Related Conversations