Copying highlighted cells to another cell

[MasterN]

Hello All,

first off I really am imprssed by the great effort in the forum ...


I made a macro that uses conditional formatting to highlight duplicates,triplicates, etc .
now I want the macro to look for the now-highlighted (red) -duplicate- cells and put them in AA column and remove the duplicates from the AA column ...and look for triplicate (Yellow) cells and put them in AC...the thing is I can`t get it to copy the highlighted values to the specified columns....here is the code I have so far

Please Login or Register  to view this content.

Thanks in advance

[pike]

Hi MasterN,
Welcome to the forum.
You cant select condital format cells by colour.
You will have to select the cells with the condition formula and the cells value

[MasterN]

Hi MasterN,
Welcome to the forum.
You cant select condital format cells by colour.
You will have to select the cells with the condition formula and the cells value

Thank you Pike for the quick reply .

well .. the cell values are variable .. the data are fed to excel through another program .. so everytime Excel is opened the data is changed .. so I can`t select the cells with their values .. how can I select the cells with the condition formula ?!

EDIT: something came to my mind ... is the problem with color ?! .. should I get the condition formula to change something else in the cell in the first place ?! if so , what should be changed !!

[pike]

No, the conditional format cell colour is different to the cell.interior.color, which you can select cells by

[MasterN]

No, the conditional format cell colour is different to the cell.interior.color, which you can select cells by

I got what you mean .. is there a way to get Conditional Format through VBA to change the Cell.Interior.Color ?! ..

I tried experimenting with

Please Login or Register  to view this content.

in the If statement .. but still I can`t get it to work

[pike]

you could use a find synatx
the first find to AA
run the find again these go to AC

[MasterN]

you could use a find synatx
the first find to AA
run the find again these go to AC

What should the find syntax look for ? .. and then what would the implementation of the loop be like ?

[MasterN]

Ok ... I almost solved it ...here is what I came up with so far ...as my code highly depend on conditional formatting ...I thought then I should change the conditional format highlight into the cell.interior color ...this function change the FormatConditions.Interior.Color into Cell.Interior.Color

Please Login or Register  to view this content.

I then applied a for loop .. to get the resulting cells into a column

Please Login or Register  to view this content.

The bad thing is ..well actually two bad things .. ... first off .. it takes almost 4 minutes to finish and it freezes Excel during calculating the Macro ... the second thins is that I got the results scattered around in almost 12 columns .. every other column .. instead of ONE column as I intended for it to do . I can`t tell what is wrong with the for loop ..

[pike]

MasterN
try this code it may be faster

Please Login or Register  to view this content.

[MasterN]

I think I reached a point that I need a fresh eyes to look at the code as I can`t figure out why the code does what it does ... here is what I`ve done so far ..

1- the code applies three formulas of Conditional Formatting . ( check)
2- then look for cells with that conditional Format applied to .. and convert the FormatConditions.Interior.Color into cell.Interior.Color . (check)
3- Delete the Conitional Formatting from the cells (check)
4- look for cells with "red" color and put them in the column AA ( start of the problem here I guess )
5- look for cells with "Yellow" color and put them in AC .( the code doesn`t do this step at all)
6- look for cells with "green" color and put them in AE
7- delete duplicates from column AA .
8- delete Duplicates from Column AC
9- delete Duplicated from column AE


I attached the sheet with the macro in it .. hopefully someone will be able to spot what is wrong with the code .. thank you in advance

here is the code as well

Please Login or Register  to view this content.

[pike]

did you try the code?

[MasterN]

did you try the code?

I was waiting to solve the issue first as I didn`t want to add another problem while trying to figure out why it isn`t working the way it is supposed to .I was thinking get it to work right then worry about efficiency .. this thread was about the highlighting issue and I believe it is solved ( I just wrote another thread to keep it in particular and mark this thread solved ..I was wondering how to do so though )

[pike]

Hi MasterN,
You can't copy conditionally formated coloured cells.

[MasterN]

Hi MasterN,
You can't copy conditionally formated coloured cells.

Hello Pike,

the cells are no longer conditionally fomated .. the custom function ConditionalColor .. changes the Conditional Format coloured cells into a normally colored cell .. as I already mentioned in my previous post ... now the Cell.Interior.Color = FormatConditions.Interior.Color ... I tested it and it works ( please check the function in the previous post )... the problem isn`t in the conditional format anymore .. this is why I mentioned that I want to mark this thread as solved ...

[pike]

Hi MasterN,
Appoligises,I didnt read that ,very very good idea. I have to subscribe to the other thread .

[MasterN]

Thank you, Pike ..

here is the link to the other thread hopefully you can help with it .. I tried to explain the code the best I can .. so someone would be able to go through it and help get it over with

http://www.excelforum.com/excel-prog...al-format.html