Recognising a format change

[johnnic]

Is there any way in Excel 2007 to use a function such that a particular format is recognised? For example, can I use SUMIF with the criteria being a specific format rather than simply a value, etc?

In my case, I want to sum a range of numbers preceding a cell where the format changes.

 

[DonkeyOte]

The first thing to note is that manually altering the format of a cell is not a Volatile action and as such does not invoke a recalculation.
The upshot of which is that your model will always be open to error.

If however you are using Logic to determine the format (i.e. Conditional Formatting) you can look to ape that logic in native functions to count the formatted cells.

 

[johnnic]

Thank you, but I'm not sure I'm much the wiser.

I have a spreadsheet with several rows containing data. Dependant on the circumstances, I regularly change the format of a given row by simply changing the background colour (although I could just as easily do it some other way, such as italicising the entire row, or whatever - it is simply a way for me to visually recognise that something has happened that I wish to identify).

I now wish to extract a particular piece of data from the spreadsheet that is contained in a row with a specific background colour.

For example, I want to answer the question "What is the value of cell D4 when Row 4 is coloured red?"

Any specific thoughts?

 

[DonkeyOte]

I regularly change the format of a given row by simply changing the background colour (although I could just as easily do it some other way, such as italicising the entire row, or whatever - it is simply a way for me to visually recognise that something has happened that I wish to identify

I now wish to extract a particular piece of data from the spreadsheet that is contained in a row with a specific background colour.

You would need to use VBA (a UDF) and as previously outlined the approach would be flawed from inception irrespective of whether or not the UDF is Volatile (a Volatile UDF would reduce rather than remove risk).

It might not be the answer you want to hear but in reality you should not use manual formatting as basis for differentiation for the above reasons.

You would be far better served denoting values of interest using a key in an adjacent column - for ex. instead of formatting row red enter x in a given column. You can use the key column as basis for subsequent calculations (SUMIF, IF etc...) - this removes risk of erroneous values in your model and the need for a Volatile UDF in the first instance.
Visually - you can use the "x" flag in a Conditional Format rule to highlight the row red (as before).

 

[johnnic]

Thank you, that is very helpful and I can see how it would lead me to where I want to be.

You said, in part "Visually - you can use the "x" flag in a Conditional Format rule to highlight the row red (as before)."

Would you mind explaining to me how you actually do that? Using a Conditional Format rule, how do I set Row 4 to colour Red when Cell A4 is marked "x"?

(Edited: I think I've worked it out - THANKS!!)

 

[DonkeyOte]

With row 4 highlighted your Conditional Format rule would be:

=$A4="x"
format as red fill

Of course you could apply the above to multiple rows simultaneously, however, you should still look to avoid overuse of Conditional Formatting as it is "super-volatile"

Personally, I would be inclined to restrict the Conditional Formatting to a given number of columns per row rather than the row in it's entirety (eg A4:Z4 or whatever is appropriate to your model).