How to get entire row in MS Excel colored? (Conditional Formatting)

Ajit Basrur

Staff member
Admin
#1
I am preparing a tracking template in MS Excel in which there will be several action items for tracking. Once the action item gets Completed, is it possible that the entire row gets colored as Green ? Likewise, "Underway" as yellow color and "Overdue" as Red color ?

I have attached the template - any improvement ideas also most welcome !

Appreciate urgent help pl :)
 

Attachments

Elsmar Forum Sponsor
H

Hodgepodge

#2
Re: How to get entire row in MS Excel colored ?

Per your attached file:
1) Highlight Columns A through G (or all the columns you like).
2) On the Format Tab | Conditional Formatting
3) Where it says "Cell Value is", Choose: "Formula is" on the Drop Down. Tab over & Enter this formula: =$F1="Completed"
4) Select the "Format" button. Click the "Patterns" Tab
5) Choose your color. Click the "Add" button.
6) Repeat the above using the remaining text criteria ("Underway" & "Overdue")
7) Now change the Data Validation list criteria removing "Completed (Green)" and make it just "Completed". Repeat for remaining criteria.

I have attached a copy of your spreadsheet, modified per the instructions above. If you highlight Columns A through G and choose Conditional Formatting on the Format Tab, you can see how the formula is entered.
 

Attachments

Ajit Basrur

Staff member
Admin
#4
Re: How to get entire row in MS Excel colored ?

One more question on this - will the "overdue" cell turn red color based on the computer system date ? Let me illustrate this point - if I have an action item that is due on Sep 9 and once the computer clock turns to Sep 9, will the status turn to red color ?
 
J

JaneB

#5
Re: How to get entire row in MS Excel colored ?

Conditional formatting is a wonderful thing, isn't it Hodgepodge? I love it.
 
H

Hodgepodge

#6
Re: How to get entire row in MS Excel colored ?

One more question on this - will the "overdue" cell turn red color based on the computer system date ? Let me illustrate this point - if I have an action item that is due on Sep 9 and once the computer clock turns to Sep 9, will the status turn to red color ?
Yes, Excel will use the time and date based on your computer's time.

Conditional formatting is a wonderful thing, isn't it Hodgepodge? I love it.
;)
 
Thread starter Similar threads Forum Replies Date
Ajit Basrur Need help in Conditional Formatting for the entire row Excel .xls Spreadsheet Templates and Tools 3
Marc Informational Louvre puts entire collection online - 3-2021 World News 0
B Is it necessary to conduct product audit of entire product which is manufactured in the company as per IATF IATF 16949 - Automotive Quality Systems Standard 4
Ashland78 IATF 16949 9.2.2.1 - How often do we have to audit entire IATF Manual IATF 16949 - Automotive Quality Systems Standard 4
JoCam Acquired Medical Devices - Company A buys the entire rights EU Medical Device Regulations 4
M Treatment system data analysis - Subject to CFR part 11? Entire system? Qualification and Validation (including 21 CFR Part 11) 7
K Can I use ANSI/AQL for the Total Number of Containers for the entire Lot? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 1
B Internal Audit/Checks (7.1?) - Entire System Yearly? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
A Should I submit the entire copy of my software in a 510(k) submission?? US Food and Drug Administration (FDA) 7
T Six Sigma Project - The entire Corrective Action Process Six Sigma 3
A Receiving Inspection - One drum leaks - Should entire lot be rejected? Inspection, Prints (Drawings), Testing, Sampling and Related Topics 5
P Developing a SOW for Entire Site Calibration Requirements General Measurement Device and Calibration Topics 3
R Does AS9100C require you to have SPC throughout your entire facility? AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
J Knowledge of CAR/PAR Procedures - Should the entire organization know/understand? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
N Sample Selection for MSA Study - Entire range of the Tolerance? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 4
G 510(K) for an Entire System vs. Separate 510(K) for each Sub-System 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 10
M Quality estimation of one and an entire set of elements Statistical Analysis Tools, Techniques and SPC 2
A Pin Gage - Gage R&R Studies - One Pin or Entire Set? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 2
D What does the entire process range mean? (MSA manual page 73) Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 7
E Is an entire PFMEAs considered Proprietary? FMEA and Control Plans 12
S Bogus ISO certification?? Entire systems totally ineffective or missing ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 10
K Applicability of requirements of entire section 7.4 of TS16949:2002 IATF 16949 - Automotive Quality Systems Standard 2
J Determining effectiveness of APQP Process throughout our entire organization Preventive Action and Continuous Improvement 4
D Audit Crisis - The entire management has left General Auditing Discussions 9
J In House Laboratories - Do we have to create an entire quality system for the lab? General Measurement Device and Calibration Topics 2
D ROW Approval Matrix Other Medical Device Regulations World-Wide 2
smryan How to reference a row from a cell value in Excel? Excel .xls Spreadsheet Templates and Tools 2
O Need to Create Macros so that it can copy data to the next row.... Excel .xls Spreadsheet Templates and Tools 5
L Turning one continuous row to columns in Excel 2007 Excel .xls Spreadsheet Templates and Tools 4
Marc Rut Row - Water running low in LA - What does this mean? Feb 2008 World News 0
C SPC Chart Run Rules - 7 in a row vs. 8 in a row Statistical Analysis Tools, Techniques and SPC 3
J Incoming Inspection Records using Excel File ISO 13485:2016 - Medical Device Quality Management Systems 2
S 95% UCL - Attaching an excel for recovery study Statistical Analysis Tools, Techniques and SPC 0
T EXCEL - How do I make the cell stay blank until a date is entered in N7? Excel .xls Spreadsheet Templates and Tools 6
Ed Panek Validating Excel (XLS) formulas 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 7
C Template for Excel Validation Reliability Analysis - Predictions, Testing and Standards 6
R Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
S Issuing of CoC to Customer in a Word or Excel format ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Ajit Basrur Track audit findings on Excel tracker Excel .xls Spreadsheet Templates and Tools 9
J Does anyone have an excel IATF 16949 Internal Audit checklist I could use? IATF 16949 - Automotive Quality Systems Standard 7
S Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
P Commercial excel templates that calculate overhead, hourly rates, fee proposal Excel .xls Spreadsheet Templates and Tools 0
I Cannot Export multiple sections from Excel to PDF without losing pagination Excel .xls Spreadsheet Templates and Tools 23
M Matching two matrices in Excel Excel .xls Spreadsheet Templates and Tools 5
J Level 3 KPI Excel Template Manufacturing and Related Processes 1
bobdoering Informational New AIAG PFMEA Process - Excel Template Attached FMEA and Control Plans 23
GreatNate Excel PPAP Tracking templates or tool wanted APQP and PPAP 1
E Part 11 Compliance, Excel living documents (i.e. document master list, equipment list, approved supplier list) Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 3
J ISO 9001:2015 Excel Gap Analysis wanted ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
B AS9110 Audit Check Sheet wanted Excel or Word Document AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2

Similar threads

Top Bottom