 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / November 2007move formatted cells
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 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.  SignatureGary''s Student - gsnu200758 > hi, > [quoted text clipped - 11 lines] > > patrick AWESOME!! cheers thats a great idea! only problem is it seems to keep repeating for ages? > For example: > [quoted text clipped - 31 lines] > > > > patrick 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!! > AWESOME!! > [quoted text clipped - 36 lines] > > > > > > patrick oh and the code i posted before was wrong x sorry x > 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: [quoted text clipped - 58 lines] > > > > > > > > patrick 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 SignatureGary''s Student - gsnu200758 > AWESOME!! > [quoted text clipped - 36 lines] > > > > > > patrick 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? > Well then, let's try a version that may be a bit faster: > [quoted text clipped - 56 lines] > > > > > > > > patrick Not trivial.. See: http://groups.google.com/group/microsoft.public.excel.misc/browse_frm/thread/de4 6e4ac01d48c8b/fd768bb674d58088#fd768bb674d58088 SignatureGary''s Student - gsnu200758 > i have an additional problem that has cropped up....... > [quoted text clipped - 64 lines] > > > > > > > > > > patrick 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? > Not trivial.. See: > [quoted text clipped - 68 lines] > > > > > > > > > > > > patrick Good suggestion... VBA can test the conditions and paint the format itself without using conditional formatting at all. Check back later today. SignatureGary''s Student - gsnu200758 > 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 [quoted text clipped - 77 lines] > > > > > > > > > > > > > > patrick 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 > Good suggestion... VBA can test the conditions and paint the format itself > without using conditional formatting at all. [quoted text clipped - 82 lines] > > > > > > > > > > > > > > > > patrick |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.