My spreadsheet has a list of well over a thousand items purchased, among which are numerous computers. Newer items are added to the next row at bottom. Column G shows a list of Warranty Expiration Dates for those computers. From time to time these expire and we haven’t noticed to check for defects in the units because we’re normally adding new purchases at the bottom of the spreadsheet, not checking on old purchases.

I’ve formatted column G to highlight each cell with a red background if its warranty ends within the next 30 days. [To do that, I selected rows 2 to the end, and using Conditional Formatting, I selected the Rule Type: “Format only cells that contain”: Cell Value between =TODAY() and =TODAY()+30. Only the color background changes to red and the font changes to italics. Text itself does not change.] Now, the cells with expiring warranties turn red—but I don’t normally see those cells.



How do I format the column heading to toggle its text and color based on conditionally formatted cells below it AND include a count of those changed cells? I want the column G heading (G1) to toggle between its normal title (“Warranty Expires” with yellow background) to “X Warranties almost up” with a red background and where X is the number of cells in column G currently showing its a red background. Is this possible?

You say you want a toggle, but how is that to be implemented?

As for the count, you'll need to use a formula such as SUMPRODUCT or SUMIFS that uses the same logic as your conditional formatting to tally the items.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks for pointing that out. I would like the column header to toggle its text and color whenever any of the cells below it change their background color. (Those, in turn, change color whenever they come within 30 days of their Expiration date, per their conditional formula.)

Can you attach a workbook to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]