Printing with Conditional Page Breaks - Insert Conditional Value In Header on Each Page

I don't think you need to set another cell unless it could be multiple rows in column C that trigger. If so, you could name a single cell range to contain the changed value (I'd guess you're using the change event, which gives that in its Target argument). But don't use a cell for the header. Just build it. http://www.cpearson.com/excel/headfoot.htm covers this well.

I've never tried (and don't have a printer hooked up here), but I assume you can change headers on the fly so that different print pages have different headers. If for some reason that's not true, no other ideas come to mind

BTW this would mean that the changed column C value would continue to appear in the header if there are multiple print pages, in case that matters.

Sorry for delay, just saw your post. I'm building a response - stay tuned.

Gurus / advanced users: I'm stuck on this, if someone doesn't mind reading this long post which possibly lays the groundwork and maybe comes close to solving the OP wish.

Okay, there's a difference between a "header" and "rows to repeat at top." The latter indicates the number of rows of actual cells to show atop each print page, BENEATH the header. The header is something you either build in VBA or set in Page Setup. Note the margins setting screen in page setup; if you have 1 inch as the top margin, and 0.5 for header, then the header starts printing .5 inch from the top, and the currently printing worksheet's rows 1 to 10 or 3 to 5 or whatever prints starting 1 inch from the top.

I also misunderstood what you meant by "change." My fault. I was thinking about some cell actually changing in column C, which obviously wouldn't happen amidst printing. If you do want to explore the Change event, http://www.cpearson.com/excel/Events.aspx is a great reference. (Of course, Walkenbach books are IMO the best; even gurus buy them and read cover to cover. Read Walkenbach if you want to excel in Excel.)

For what you want it's to have one of the "rows to repeat at top" contain the thing you want to show as I'll describe. The header would then be text that doesn't change, that you can set manually in Page setup.

Your header could be "Monthly Sales Report" which prints on every page .5 inches from the paper top. You can set "rows to repeat at top" to be $1:$10 . Cell B1 can be "For the month of" . C1 could be a cell defined as [a single cell] range CurPrintMonth. Your code might go
Range("CurPrintMonth") = ??? (keep reading)

At first I was guessing that your working code was BeforePrint event code, but that only happens when printing (or previewing) begins, not on every page break. I'm not familiar with an event that occurs on every page break. If there was, I would presumably be able to know the current print page number as i, and the current row to be printed atop page i is
ActiveWindow.SelectedSheets.hPageBreaks(i-1).location.Row
so you can get column C's value from
cells(ActiveWindow.SelectedSheets.hPageBreaks(i-1).location.Row,3)
which would then be ???.
(Note, that's hard coding! What if later you insert a column, making column D the biscuit? I'd make column C a range, but for brevity I'll set that aside.)
[Edit: you might actually have CurPrintMonth be in a nonprinted location, and C3 be month(CurPrintMonth), so C3 shows April, not 4/10/2013.]

But there's to my knowledge no event on each page break. So I'm stuck. Range CurPrintMonth would be the same on every page printed, which is what you're trying to modify.

Gurus, I think the key here might be some object that contains the "this point forward" variations of the header. (Yes, I'm back to varying the header, because I don't think you can have range CurPrintMonth change from page to page on printing, although it'd be great if someone showed how.) Does someone know how to set Header for page 2 (and forward) to use
cells(ActiveWindow.SelectedSheets.hPageBreaks(1).location.Row,3)
and Header for page 3 (and forward) to use
cells(ActiveWindow.SelectedSheets.hPageBreaks(2).location.Row,3)
and so forth up to ActiveWindow.SelectedSheets.hPageBreaks.Count
or a better solution, of course?

As much as I'd love for it to work, for you and for me, I don't think it will. Most likely, when you go Print or Print Preview, right after BeforePrint, it builds the whole thing. It know where the page breaks are already (obviously, seen in "page break preview mode") (and besides, already stored in H and VpageBreaks). So it can just build the whole print stream at once. If, within that process, there was an event called "OnPageBreak" or such, apparently either there isn't, or it's a secret inside Microsoft.

As to my other speculation, whether there are "this page forward" capabilities in an Excel object, like Word's design, apparently either there isn't, or it's a secret inside Microsoft. (at least on versions 97 through 07.)

Unless the magnetic poles reverse and an Excel developer actually takes enough pride in their work and job to visit someplace like ExcelForum.com - it looks like we're stuck. I hope some ingenious workaround exists - but I'm out of ideas. Sorry.