SBS - The Best Value in QMS software

Conditional Formatting for Microsoft Excel

J

jesterjay8

#1
I have created an excel spreadsheet to track rejections for our new Employee Accountability Program. I'm using conditional formatting to keep track of 2nd, 3rd, and 4th offenses with varying colors (degrees). My problem is when I add a name to the bottom of the list it highlights names previously entered instead of the one I am currently entering, so it's tracking the offenses in an ascending manner instead of descending manner. I sure hope I'm explaining this all clearly. I have attached a sample of what I am doing. If anyone can help me remedy this I would greatly appreciate it. I am using Microsoft Excel 2003 BTW.
 

Attachments

Elsmar Forum Sponsor
M

MinorNuisance

#2
in your conditional formating formulas, use absolute referencing ($) for the data range rows and relative referencing for the soruce cell row.

=COUNTIF(A$3:A$100,A3)=2

so when you copy the formating down the data range doesn't move with it.
 
A

adickerson

#3
The only way I know how do do this is with VBA. http://www.cpearson.com/excel/CFColors.htm

Once you are going to going start counting multiples and somehow keep track of those multiples to control color things get pretty complicated.

One way to do it without VBA:
If you add a column that counts the cell you are in and all previous cells you can have an ongoing count. Then use that column to control the color formatting rule. I updated you sheet for the progressive counting so you can see how that is done.
 

Attachments

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
S Excel Spreadsheet Conditional Formatting help needed Excel .xls Spreadsheet Templates and Tools 12
Ajit Basrur How to get entire row in MS Excel colored? (Conditional Formatting) Excel .xls Spreadsheet Templates and Tools 5
A Conditional Formatting - Excel Excel .xls Spreadsheet Templates and Tools 8
T Excel Conditional Format Issue Excel .xls Spreadsheet Templates and Tools 16
Buckyb AS9100C Clause 7.4.1 Appoved Supplier "Conditional" Status AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 7
B Is it normal for a procedure to begin with a conditional directive? (if statement) Document Control Systems, Procedures, Forms and Templates 30
1 Excel 2010 Conditional Formating of Text Excel .xls Spreadsheet Templates and Tools 1
N Bayes Law of Conditional Probability ("The Drunkard's Walk" by Mlodinow) Statistical Analysis Tools, Techniques and SPC 1
S Process Documentation Formatting - Requirements Document Control Systems, Procedures, Forms and Templates 3
W General Linear Model - Formatting Data Six Sigma 3
W Excel Number Formatting - Auto Decimal Place by What You Type Excel .xls Spreadsheet Templates and Tools 16
B Can you guys help me figure out formatting a 510K Other US Medical Device Regulations 11
Q IT Instruction - use same formatting as ISO procedures? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 4
R Quality Manual - Formatting (print) Question Quality Management System (QMS) Manuals 6
smryan Excel Cell or Text Formatting help Excel .xls Spreadsheet Templates and Tools 27
Jen Kirley Formatting an Excel .xls spreadsheet to alarm out-of-calibration status Excel .xls Spreadsheet Templates and Tools 11
Q FMEA and Risk assessment in Microsoft Access FMEA and Control Plans 6
J Microsoft Smart Art Turtle Diagram Process Maps, Process Mapping and Turtle Diagrams 9
M Informational Microsoft Built a Bot to Match Patients to Clinical Trials Medical Device and FDA Regulations and Standards News 0
J Microsoft Teams to manage Internal Audit System? Internal Auditing 3
D Microsoft Excel database to Stand-alone software Calibration and Metrology Software and Hardware 3
Q Controlling Multi-Tab Microsoft Excel Forms Excel .xls Spreadsheet Templates and Tools 5
A Free software similar to Microsoft Project - Any suggestion? APQP and PPAP 5
Marc ASQ Linkedin Group - Microsoft Sharepoint discussion - June 2017 Quality Assurance and Compliance Software Tools and Solutions 0
Marc Microsoft to rip up P2P Skype, killing native Mac, Linux native apps World News 0
Marc Microsoft just bought LinkedIn For $196/Share World News 9
I How to build a Microsoft Access MDB Database for Document Control Document Control Systems, Procedures, Forms and Templates 6
M Active X and latest update from Microsoft - Dec 2014 After Work and Weekend Discussion Topics 4
N Microsoft Word - How to make high-light go away after text is put in Excel .xls Spreadsheet Templates and Tools 5
B How to validate Microsoft Excel Spreadsheet Calculations for the Cpk value Capability, Accuracy and Stability - Processes, Machines, etc. 9
O Chi Square Normality Test using Microsoft Excel Excel .xls Spreadsheet Templates and Tools 2
insect warfare IRS pays Microsoft for Windows XP support After Work and Weekend Discussion Topics 2
Marc Microsoft to push out XP patch on March Patch Tuesday - 2014 After Work and Weekend Discussion Topics 19
T Microsoft Access Database to create a Simple Calibration Certificate General Measurement Device and Calibration Topics 6
V Alternative to Microsoft Office - Open Office vs. Google Docs After Work and Weekend Discussion Topics 26
Jim Wynne Have you tried Microsoft Windows 8? After Work and Weekend Discussion Topics 25
AnaMariaVR2 Guide to Microsoft Excel for Calculations, Statistics, and Plotting Data Excel .xls Spreadsheet Templates and Tools 11
M Microsoft Excel - Trouble with Auto-Entry of Dates Excel .xls Spreadsheet Templates and Tools 13
H 4.2.3 Document Control using Microsoft Sharepoint - Visibility of Draft Documents ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 3
L 21 CFR Part 11 Requirements - Microsoft Excel Add-on Functions 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 6
Q Looking for Microsoft Dynamics AX Training Videos for Inspection Journals Manufacturing and Related Processes 1
Colin Changing the Colour of a Date in Microsoft Excel Excel .xls Spreadsheet Templates and Tools 4
X Microsoft Excel 2003 Formula Restrictions Excel .xls Spreadsheet Templates and Tools 9
michellemmm Pagination in Microsoft Word Documents Document Control Systems, Procedures, Forms and Templates 21
H AS9101D Microsoft Word Format: Worth It? AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
P Creating a Traceability Database with Microsoft Access Document Control Systems, Procedures, Forms and Templates 7
Marc Microsoft Losing Big To Apple On Campus After Work and Weekend Discussion Topics 18
P Magic with Microsoft (things that could not be explained) Funny Stuff - Jokes and Humour 8
Marc Apple Dethrones Microsoft As World's Biggest Tech Company After Work and Weekend Discussion Topics 6

Similar threads

Top Bottom