move formatted cells

[Guest]

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick

 

[Guest]

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.

 

[Guest]

AWESOME!!

cheers thats a great idea! only problem is it seems to keep repeating for
ages?

 

[Guest]

its ok, what kept happeneing is it was deleting all cells that werent yellow
so was going on for ever! made the following adjustment and it works:

Sub movit()
Set s1 = Sheets("Sheet")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
ElseIf Cells(i, "A").Value = " " Then

s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

THankyou again!!

 

[Guest]

Well then, let's try a version that may be a bit faster:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub

 

[Guest]

i have an additional problem that has cropped up.......

the coloured cells are coloured because of conditional formatting and this
causes problems when the cells are coppied and deleted.

any ideas how to get round it? is it possible to remove the conditional
formatting but keep the cells coloured?

 

[Guest]

oh and the code i posted before was wrong x sorry x

 

[Guest]

Not trivial.. See:

http://groups.google.com/group/micr...4ac01d48c8b/fd768bb674d58088#fd768bb674d58088

 

[Guest]

that looked fairly complicated and not sure it will work for my data as it is
conditional on a formula? the conditional formula i am using is

=countif(A:A,E3)=0

is it possible to recreate this function with VB so i'm not applying
conditional formatting to a cell just formatting it if it follows a certain
condition?

 

[Guest]

Good suggestion... VBA can test the conditions and paint the format itself
without using conditional formatting at all.


Check back later today.

 

[Guest]

I have just tried it another way, taking a step back from the conditional
formating formula.
i inserted a row before the data and for each row entered the countif
formula i used with the conditional formatting. this shows a 0 next to the
cells that would have been yellow. from here i can now use the code as before
but substitute colour = 6 with value = 0

thankyou for your help