Extracting certain values from an array into a new column(s)

[CalcNovice]

Hello everyone. I was very grateful for the advice I got for a problem yesterday and hoped for the same response to a new problem (actually the next stage of the old problem).

So, I have an array where certain values are flagged up in red with conditional formatting. What I'm aiming for is to take those red hits and collect the values in a new column or columns. If there's a red value in row 1 of my array I want it copied to, say, cell X1. If there's a second red cell in row 1 of the array it should end up copied to cell Y1, and the third to cell Z1, etc. If row 2 of the array has no values popping out in red, then cell X2 remains empty.

Or, to put it another way, to take my array and remove all cells whose values don't meet my formatting condition, to end up with a collapsed field of interesting (red) results that will end up looking flush left and jagged right.

I hope that explains it clearly enough. Any suggestions much valued!

-Pete

 

[MickG]

Try this:-
NB:-This code will return values from conditional format range if true.
This means Conditions 1 to 3.
If you only want one condition you will have to alter the code accordingly.

[COLOR="Navy"]Sub[/COLOR] MG30May07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, con [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray, Res [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
ReDim Ray(1 To Rng.Count, 1 To Columns.Count)
MsgBox Rng.Address
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
 Dn.Select
      [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
      [COLOR="Navy"]For[/COLOR] con = 1 To 3
            Res = Evaluate(Dn.FormatConditions(con).Formula1)
          [COLOR="Navy"]If[/COLOR] Res = True [COLOR="Navy"]Then[/COLOR]
             Ray(Dn.Row, Dn.Column) = Dn
          [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] con
[COLOR="Navy"]Next[/COLOR] Dn
Range("X1").Resize(Rng.Rows.Count, Rng.Columns.Count) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[CalcNovice]

Thanks Mick, but can it be done just with functions?

 

[MickG]

I don't think so !!!
Mick

 

[Datsmart]

The color of a cell via Conditional Formatting can not be compared the same as a cell with its' "Properties" changed via Formatting. See the available custom Functions available on C Person's site here:
http://www.cpearson.com/excel/CFColors.htm

 

[CalcNovice]

Cheers John, will take a look..

 

[T. Valko]

Thanks Mick, but can it be done just with functions?

Can you post some sample data and show the result you expect.

It sounds like it should be possible using formulas as long as there isn't 1000's of rows of data. You would extract the data based on WHY the cells are formatted red. So, you'd also have to tell us why the cells are colored red.