Annual Expiration Conditional Format

**Richard Buttrey** · 03-17-2015, 01:21 PM

Hi,

There are quite a lot of redundant cond. formats and I'd like to start from scratch. Please explain the rules for the red,green or amber(?)

Can you clarify the new column D. Is this to be a formula or are you manually adding the yes/no?
In any case just set the conditional format in D3 using the 'Format cells that contain' and enter "No", set the format to red and apply it to the whole of column D.

**Winzier09** · 03-17-2015, 02:27 PM

Mr. Buttrey,

I cleaned it all up a bit. So I have 5 columns A, B, C, D, & E. I have configured all the colors for all of the cells, but the final issue is getting the name to turn either red or green. Example, if Column B and D are red I want the name to turn red. If all the columns are green the name stays green. If the user is red on something his name is red whether its one red or all reds.

Current CopyDate Seperator_TEST.xlsx

Thanks for the quick responses. You've helped a ton already!

**Richard Buttrey** · 03-17-2015, 08:08 PM

Hi,

I'm puzzled by all your conditional formats, circa 35 in the case of each cell in column D. Futhermore I don't understand why one or two of them use a MOD(Row(),2) formula since these are nothing to do with dates and will in the absence of other higher priority CFS create a 'pyjama' stripe effect on successive cells.

As I say I'd like to start with a clean sheet with no CFs and add new ones as appropriate. Hence I need to understand the precise rules for painting any cell red or green. What you currently have seems overcomplicated.

When it comes to the CF for the single name cell A3 and if I understand the requirement, that's going to need either a helper column or two or some fancy array formula to count the number of different coloured cells.

**Winzier09** · 03-17-2015, 10:55 PM

My apologies. I was using copy and paste function instead of fill down from first cell in each row. There should be the correct number of conditional formats. The MOD(Row(),2) was from a different post you had, which I took out after realizing its function.

I have to sift over 1,200 users accounts to ensure they are compliant. A company website spits out the raw data, but I have to sift through manually to see if they are compliant. (i.e, annual date expired, they have the document yes/no) it takes hours looking through this way.

Instead I copy and paste the data, and I can visually see red bad, name turns red because they aren't compliant, and then I can see which of the four criteria is making them non compliant.

1. Do you know how to make the name green or red according to the other columns?
2. If I copy and paste raw data into the spreadsheet it sometimes has blank cells where they haven't uploaded the training document. I want the cell to know if its blank to turn red and say "upload document"

I put notes in A3, A4, and B5 in the spreadsheet to clarify better. Thanks

Date Seperator_CF Clean.xlsx

**Richard Buttrey** · 03-18-2015, 06:53 AM

Hi,

Does the attached help. See the sheet I copied.

Formatting can't introduce or change anything in a cell hence you can't paste a blank cell into column B and then have it display "Upload document", at least not without running a macro to subsequently add the comment to any relevant cells. I've therefore added a helper column to show this.

Incidentally a more efficient formula to find a date any number of days/months after a given date are formulae like

=EOMONTH(A1,11)+Day(A1)

This will find the last day of the month 11 months on from the base date plus the number of days represented by the date in the month.