• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Move color cells down to selected cell

S

sparcle

New Member
Hi All,


i have problem in moving cells.

Problem is i have 2 col A & F.

F col associate with date values & Col A has some color cells.I want to move down those color cells to a selected non-color cell when run a macro.


I asked this question in thread as a sub question.
 
Hi ,


Sorry about the delay in replying to your earlier post. Can you clarify how this movement should happen ?


1. Should the colored cells alone move , or should the entire row be moved ?


2. If only the colored cells move down , what should happen to the other cells in that column ? Should they move up , or should the colored cells be shifted below the existing data in that column ? For instance , suppose cell A7 is to be moved ; if the data already exists in the range A2:A17 , should the data in A7 be put in A18 , or should the cells A8:A17 be moved up , and A7 data be put in A17 ?


3. What about the cells which are colored , but which are below the selected cell ? Should they remain in their current locations ? Or should all colored cells be bunched together ?


Narayan
 
thank you NARAYANK991 for repose to this.


Ok i explain it.


1. The entire row be moved.


in my work book if i select A2 cell or B2 cell run macro then the color cells i.e. A7 to A10 will move below the Cell A2 with sorting order of B col.


http://www.2shared.com/file/nxSQsABK/color.html
 
Hi ,


Can you try out the following macro ?

[pre]
Code: 
Public Sub Move_colored_cells_rows()

' Instead of moving the entire row , 10 columns will be moved
' If more columns are to be included , modify this number
Const NUMBER_OF_COLUMNS = 10

Dim selected_cell As Range
Dim temp_cell As Range
Dim data_range As Range

Dim from_array As Variant
Dim to_array As Variant

ThisWorkbook.Worksheets("Sheet1").Activate
Set selected_cell = ActiveCell.EntireRow.Cells(1, 1)
selected_row = ActiveCell.Row
row_counter = 1

Set data_range = ThisWorkbook.Worksheets("Sheet1").Range("A2:B12")
data_range.Select
number_of_rows = Selection.Rows.Count

Set temp_cell = Selection.Cells(1, 1)
For i = 1 To number_of_rows
curr_cell_color = temp_cell.Offset(i - 1).Interior.ColorIndex
If curr_cell_color <> xlNone Then
curr_row = temp_cell.Offset(i - 1).Row
If curr_row > selected_row Then
from_array = temp_cell.Offset(i - 1).Resize(1, NUMBER_OF_COLUMNS).Value
to_array = selected_cell.Offset(row_counter).Resize(1, NUMBER_OF_COLUMNS).Value
temp_cell.Offset(i - 1).Resize(1, NUMBER_OF_COLUMNS).Value = to_array
selected_cell.Offset(row_counter).Resize(1, NUMBER_OF_COLUMNS).Value = from_array
temp_cell.Offset(i - 1).Interior.ColorIndex = selected_cell.Offset(row_counter).Interior.ColorIndex
selected_cell.Offset(row_counter).Interior.ColorIndex = curr_cell_color
row_counter = row_counter + 1
End If
End If
Next
End Sub
[/pre]
Please note the comment at the beginning of the macro ; I am moving only 10 columns ( defined by the value of the constant NUMBER_OF_COLUMNS , which I have given the value 10 ). If you need to include more columns in the move , please increase this value to whatever you need.


Narayan
 
@NARAYANK991,


Thank you sir.Thank you very much.

It take a week wondering about what to do.

Take bow from me.


Thank you.
 
@NARAYANK991,

hello sir,

i have some problem when i used this macro in following sheet.

http://www.2shared.com/file/Cd9w25vD/color.html


color cells move up but it does not push down the cells.


As here when i select col B6 & run macro color cells move to below B6 but the cell B7 does not push down 2 cells.
 
Hi ,


I saw your workbook , and I could not understand your requirement ; can you explain with specific reference to cell addresses ? I am copying and pasting the input data from your file ;

[pre]
Code: 
1	5/18/2012 19:24
2	5/20/2012 2:00
4	5/26/2012 0:00
6	6/10/2012
5	5/27/2012 12:00
3	5/19/2012
7	6/8/2012 0:00
8	6/11/2012 0:00
9	6/12/2012 0:00
From the above , placing the cursor on :

[pre][code]2	5/20/2012 2:00
[/pre]
to get the following ( from Sheet2 of your file ) :

1 5/18/2012 19:24
2 5/20/2012 2:00
4 5/26/2012 0:00
6 6/10/2012
3 5/19/2012
5 5/27/2012 12:00
7 6/8/2012 0:00
8 6/11/2012 0:00
9 6/12/2012 0:00[/code][/pre]
I am not able to understand what is to be done. Can you explain ?


Narayan
 
sir,

when color cells move up the order then they do not push down the cells below.

As in above when color cell 4 & 6 move up the order below 2 then cell 3 go to position 6.

i want when cells move up then the cells below them just push down.


No 3 cell just push down two cells below as 4 & 6 cell move to cell no 2.
 
Hi Sparcle,


I am following this post, when I told you to use Conditional Formatting.. and from there after continuously reading the same, but please dont mind, still your requirement is not clear to me..


1st Situation:

Code: 
1	2     <C>4</C> <C>6</C>	3	5	7	8	9


where 4 and 6 are colored cell and current cursor position in 2


so what do you want.. after succesfully running the macro..
 
Hi Debraj Roy,


http://www.2shared.com/file/ReYHgRyX/color.html


if you look at the above workbook when i run macro it result what is in sheet1 but i want the result which is in sheet2.


Problem is when current cursor position in 2 & macro run 4 and 6 are colored cell moved below cell 2 but cell 3 moved to cell 6 position.


I want when 4 and 6 are colored cell moved below cell 2, cell 3 moved down to two position.
 
Hi ,


I am not able to understand your requirement. I have copied the original input data from column A :

[pre]
Code: 
1
2
4
6
5
3
7
8
9
where 4 and 6 are colored.


Now , if you place the cursor on 2 , and run the macro , the macro is supposed to move the colored cells beneath the cursor ; since in your situation , the colored cells are already immediately under the active cell ( the cell where the cursor is placed ) , nothing should change.


If you now say that the macro should change the order as follows ( shown on Sheet2 of your file ) :

[pre][code]1
2
4
6
3
5
7
8
9
can you say what should be the logic for moving 3 to the position beneath 6 ? I can see that the numbers under 6 are now in ascending order , but is this your requirement ? Suppose the original order had been :

1
2
4
6
5
3
8
9
7
[/pre]
would it also become :

1
2
4
6
3
5
7
8
9[/code][/pre]
Can you clarify / confirm ?


Narayan
 
Sir its my fault that i can't explain it properly.


After using code it works as when cursor on 2 , and run the macro the colored cells are immediately under the active cell


But problem is when color cells move to under the active cell cell 3 do not just push down two places below , it move to cell no 6 which is originally placed before run macro.


I want the color cells only inserted below active cell.
 
Hi ,


I am still confused !


Let us leave ordered numbers aside , and take some random alpha characters ; then there cannot be any assumptions.


Suppose our data is from cell A5 through B13 ; suppose the data in column A , starting from A5 is the following alpha characters :


X , U , A , G , H , J , Y , Z , B


Now , let us assume the colored cells are the cells containing G and Y i.e. cells A8 and A11.


Now consider the following situations :


1. The cursor is placed on A7 and the macro is run


2. The cursor is placed on A8 and the macro is run


3. The cursor is placed on A10 and the macro is run


4. The cursor is placed on A12 and the macro is run


What should be the resulting sequence in each case ? Can you explain ?


Narayan
 
Hi ,


Things are quite complicated , more so than originally posted.


1. Case 1 - When the cursor is placed on A7 , and the macro is run , G does not move since it is already just beneath the active cell. Y moves up to just beneath G. H , J , Z and B are pushed down , in their original order.


2. Case 2 - When the cursor is placed on A8 , and the macro is run , nothing changes , since the cursor is on a colored cell. This was not envisaged earlier , and a check for this should be incorporated in the code.


3. Case 3 - When the cursor is placed on A10 , and the macro is run , G moves down to beneath the active cell. Nothing happens to Y since it was already below the active cell.


4. Case 4 - When the cursor is placed on A12 , and the macro is run , both G and Y move down.


The additional fact that has been mentioned is that when the colored cells are being moved , they should be sorted in ascending order of time.


I think the additions will take some time ; please be patient.


Narayan
 
Sir,

we can omitted case 2 as it not necessary.


I think if we can store colored cells in an array then inserted to the cell below activate cell i.e. activecell.offset(1,0) then it will produce the result.
 
Hi ,


OK. But the code for placing the colored cells in ascending order has to be added. It is not there at present.


Narayan
 
You must log in or register to reply here.
Back
Top