Conditional Formatting Formulas for Date, Text and Values/Numbers

**pblobe** · 05-08-2013, 10:02 PM

I have a project I am working on where I need to highlight specific (rows) with a cell fill for values that meet certain conditions. I am sure I need to use conditional formatting using a formula but I need help with the formulas to get Excel to do what I want. Attached is my sample file showing what I am trying to do (if you can select one of these example and highlight (cell fill) for the whole row vs just the single cell.... and with the remaining 2 samples show me the formula for the individual cell fill that will do what I need).

1. I want to highlight the most recent date of a document/fill change - Column A (May 2nd records are the most recent info available) - full row highlighted
2. I want to highlight just the first (or one) record of same category/record and highlight them once in each instance - Column B - single cell highlighted
3. I want to highlight any Qty/values that have changed since the last update (is there a way to do this besides using a Date like in Column A) - Column D

Is it possible to do this or would I need to write a more complicated SQL or other statement?

Appreciate any help you can offer :-).

Paula

**FDibbins** · 05-08-2013, 10:24 PM

for 1...

1. highlight the range you want to apply the conditional formatting to (A2:F11)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =$A2=MAX(IF($B$2:$B$11=$B2,$A$2:$A$11,0))

for 2
Repeat above for range B2:B11
=COUNTIF($B$2:B2,B2)=1

For 3
Im not sure I understand what you are trying to do? based on your sample, you have every May 2 entry highlighted?

**pblobe** · 05-08-2013, 11:12 PM

Thank you so much for the formula's. I think I am seeing how this works. I don't think I will need to use all of these at once/in one spreadsheet. I am just anticipating what will be asked of me as we find we can highlight changed fields with color.

For Example #3, I am assuming the Column A with the Date will not be included/provided in some of my spreadsheets. The qty will be periodically updated and I will want to highlight those who's Inventory number has changed since it was checked (updated) last. I don't know if this is even possible. The work around would be to insert a date field that would indicate when the record/row was last updated?

I hope I am not still confusing you. REALLY appreciate your help.

Paula

**FDibbins** · 05-09-2013, 12:26 AM

OK so what would you consider the "same inventory"...
office supplies - paper clips - 10 only appears once

In fact if you look at the combination of Category and Product, each combo only appears once in your sample. I cant see the purpose of comparing 2 sets of "Office Supplies" to see if the QTY has changed, if 1 is paper clips, and the other is staplers?