Hey guys!!! so I'm on my last leg of my project and it only took me 4 questions. Not bad for a first timer. I think....
Anyway so now I have a spreadsheet and the cells in colomn B are all different colors. lets just say red, blue, green, and purple. so now I need to sort by color. now I only change one row at a time so; I do some stuff It causes cell B to change color, lets say purple, (got that already). Now I want to move it up or down (99% of the time down) in the sheet untill it is in that color, purple for this example, and then sort all the purple cells in alaphebetical order according the values of colomn B.
yeah i really don't like that method..... I don't want to make an extra column, it sorts by colors but then doesnt sort the colors themselves I already have my color order it's 36, 35, 38, 8, 39, 4, 33, 3, 43, and xlnone
not to mention there is a lot of stuff you have to do manually in there and the point of the macro is to automate everything. Since I am only changing one line at a time it would be easier to cut and insert cells like I am currently doing than using this method. Thanks for the help though. Any more ideas would still be appreciated.
Is there a move down till command? or something like that? I think with something like that I could get on my way
Thanks shades that gave me almost exactly what I want. From what you have given me I came up with this. Since this is my first time I don't know the syntax for sure look it over and let me know please. Thanks.
I highlighted the parts I think are problems in Bold, colomn B is the colomn with color I am sorting by, and I moved it to the top of the sheet frist to be sure that it always comes in contact with the color I am looking for (just in case the color was up from the origional location)
A couple of quick pointers for you that ive noticed here
this wont work as it will try to find row i rather than the row which is determined by variable i
when you cut and insert then your activecell won't actually change
activecell.row.select this doesn't work you need to use the comand
Entirerow instead if row here as row just produces a number and entirerow produces a range
ive looked through your code and sorted it to this
Dim i As Integer
i = 4
' cut entire row
' insert row at top of page
' my do condition
Do Until Range("B" & i).Interior.ColorIndex = Range("B" & i - 1).Interior.ColorIndex
' Move row down
Range("A" & i - 1).EntireRow.Cut
Range("A" & i + 1).EntireRow.Insert shift:=xlDown
i = i + 1
i think this is what you were trying to achieve
to get the code box all you have to do is type [ code ]
insert your code then
[ /code ]
do this without the spaces
Dim R As Range
Dim Lastrow As Long
Dim ColorNumber As Variant
Dim clr As Variant
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("C1").Formula = "Colorindex"
'code from shades (dbforums) altered
For Each R In Range("B2:B" & Lastrow)
R.Offset(0, 1).Value = R.Interior.ColorIndex
'array set up in reverse color number
ColourNumber = Array(-4142, 43, 3, 33, 4, 39, 8, 38, 35, 36)
'blank each number then sort
With Range("C2:C" & Lastrow)
For Each clr In ColourNumber
.Replace clr, ""
Range(Cells(1, 1), Cells(Lastrow, Range("IV1").End(xlToLeft).Column)) _
.Sort Range("C2"), header:=xlYes
'delete the column with the colorindex numbers
Well that code did move it down and a lot faster too but consiquently it made my color order all messed up. This looks like it has a huge potential of helping me as long as it didnt mess my colors up. What happened?
sorry ive had a quick look at this and noticed i missed something out,
It was working fine for me before but that was with previous options set,
the way to sort this is in the line
.Replace clr, ""
Change this to
.Replace clr, "", xlWhole
Ill tell you how the code works
it inserts a row then puts the value of the colorindex of each cell in row b into that row
it then replaces the number with blanks in reverse order of your colour order
sorting each time as excel sorts blanks to the end
the mistake came when replacing the numbers it was replacing any part of the number with blanks so i.e. if it tried to clear 3, 34 would be changed to 4 etc.
the xlWhole part makes sure that the code was looking at only the whole cell so has sorted this problem