MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro & Conditional formatting error


Posted by George J on February 12, 2002 6:42 AM

I have previously asked how using conditional formatting to change a row green, I can then copy and paste only the green rows onto another spreadsheet.
The reply below was almost there.

21455.html

Unfortunately I have found that when searching for green coloured rows which have been conditionally formatted, this method doesn't work. Even though it does for manually formatted green cells.

Please help. I am way over my head ond going down fast.

George


Posted by Tom Urtis on February 12, 2002 7:06 AM

George,

My experience has been (anyone correct me if I'm wrong) that Excel does not recognize conditionally formatted cell shadings as being the "real" interior color index of the cell, because the shading you see is temporary by definition, subject to change anytime the condition changes.

One reliable way to get around this is to identify (by sort, filter criteria, or what have you) the conditionally shaded cells by their conditional criteria. If for example, your cells in column A are green because they contain a number greater than 100, then sort column A by ascending (or descending) order and copy and paste that range (currently green) that meets your criteria.

Unfortunately this is a theory explanation, not a real code solution in your actual case, but maybe this can provide some ideas for you.

Tom Urtis

Posted by George J on February 12, 2002 7:53 AM

Oh well, thanks for the assist.

: I have previously asked how using conditional formatting to change a row green, I can then copy and paste only the green rows onto another spreadsheet. : The reply below was almost there.

Posted by Juan Pablo G. on February 12, 2002 9:19 AM

Tom, just confirming you theory. [NT]

: I have previously asked how using conditional formatting to change a row green, I can then copy and paste only the green rows onto another spreadsheet. : The reply below was almost there.