You are here:

Excel/Conditional Formatting


I have a question regarding the conditional formatting. I have this 4 columns listing with continuous entries day by day by different people.
What I try to achieve is that for the specified criteria (within a date range and for particular name) the conditional formatting will highlight those entries that meet the criteria

Name       Code   Date          Rate
John B       TYT6678DDJ   10/12/2013   160.23
William H   KLK998DDS   31/10/2013   120.36
Matt R       HJKJH78DS   05/10/2014   201.23

For example if someone enters the “John B” again in row 100 and dated 31/01/2014  (date range set as after 1st January 2014 but before 31 October 2014) then the row 100 will be automatically highlighted in certain colour. To achieve this I might have a separate table for criteria setting purpose like this:

Name   From Date   To Date
John B   01/01/2014   31/10/2014

How can I set up the conditional formatting to achieve the above?


hard to answer questions that are "I might", but assuming you have a table of conditions as you show, I think I would use the multi-condition countifs function to determine if a table row entry is met.  So if the countifs returns a value greater than zero, you know that row meets a condition in your table.   

assume your table is in AB5:AD20 and column A has Name, column B has code, column C has Date, column D has Rate

for row 100


would be the conditional formatting formula.  Of course this is what it would look like for row 100 but would be entered for all your rows and columns at one time and be written relative to the activecell when you did the entry.   I am just giving an example for row 100 so it is hopefully more illustrative of the technique.

if you wanted a date range as a condition, then you would need to have a start date column and an end date column in your table.  You would add a condition for the date in the row being >= start date and another condition for the row date being <= end date

If you want to to highlight duplicates then you would use a progressive range and not use a table

so say in row 5


as that is filled down to row 6 it becomes


because the row for the terminating range part is not fixed - it is variable.  

So there are some ideas which will hopefully help you solve your problem.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.