Dynamic format copying

[rwinterton2002]

I'm trying to find a way to dynamically copy one cells conditional formatting to every cell in a column. The column title is a date that highlights when it matches the current month. I want to be able to have all of the values in the column under the date (dollar values) copy the color formatting of the date as it is updated by Excel as the months change.

[FDibbins]

when you apply the CF, couldnt you just specify the range at that stage in the "applies to" window?

also, just a word of caution...Cf is fairly resource-hungry, so applying CF to an entire column (as you suggest) could slow your worksheet down considerably

[rwinterton2002]

CF will apply the same rule to the whole range, not the outcome format. So a range of (May-7, $500, $435, $7354) with the CF of highlight if the date occurs this month will only highlight the (May-7) since it is a date in May.

[FDibbins]

depends how you set up the CF. I have put some pretty cpmplex formulae in CF

could you upload a sample?

[rwinterton2002]

Here you go. Here is the file I'm working with, minus all of the confidential info...Payments_v6.xlsx

[FDibbins]

ok, this may be all that your looking for, or it may just be step 1 in the process. take a look and get back to me

take a look at how i did the formula for the CF and maybe apply the same thing to your headings? the way i construct a CF formula is to do it inthe worksheet 1st to get a TRUE of FALSE where i need it (see E3:J3)

also note that 2007 cumulates CF conditions when you copy from 1 cell to another that already has CF applied (check the CF in H7, "next month" and "this month" is in there twice.

let me know if we are heading in the right direction

[rwinterton2002]

I see what you did on the CF for the columns, although it looks like there is a mistake on the formula, shouldn't "=MONTH(NOW())=MONTH(E$7)-1" actually be "=MONTH(NOW())=MONTH($E7)-1"? "E7" that is referenced in all of the CF formulas that you added is "Jan-12", a static header on the first column. Also, I don't know why you did the true false statements.

[FDibbins]

no, because if you absolute e ($E) then when you apply the CF dowwards, $E7 changes to $E8 etc, whereas E$7 retains the reference to row 7 when you apply the CF downwards (i know, because i made the same mistake and couldnt figure out why i was getting CF's in wierd places lol). try changing the position of the $ and see for yourself

the TRUE/FALSE statements were just to show you what i did and how, they can be deleted