Sorting Cells By Color
Aug 25, 2007
Below is a script, graciously provided by Bill Rockenbach, in order to sort a specific word "Text" between two columns.
Sub SpecialSort()
Columns("C:C").Insert
Range("C1") = "=IF(OR(A1=""Text"",B1=""Text""),1,2)"
Range("C1").AutoFill Destination:=Range("C1:C13"), Type:=xlFillDefault
Range("A1:C13").Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("C:C").Delete
End Sub
Works marvelously, How could I use this formula to sort by a specific color? ie: instead of the word "Text", to be replaced with a background color RED
View 8 Replies
ADVERTISEMENT
Nov 25, 2009
I am using Microsoft Excel 2003. I put color in my cells to show contrast with the information in my data list. I used blue and white for my cell colors. When I used sort to put my information in alphabetical order it did not only rearrange the information, it also rearranged the colored cells. My orginal pattern (before using sort) was blue, white, blue, white, blue, white. After I used sort, the pattern was changed to white, white, blue, white, blue, blue. Is there any way I can use sort to arrange my information only in alphabetical order, using sort, without rearranging the color of the cells?
View 5 Replies
View Related
Dec 20, 2008
I have Danish Office 2007.
1) Let's say I have 3 columns (horisontal) and many rows (vertical. Each row goes together, as the first column can be text that describes a transaction. The second column will be the amount of money and the third can be something else.
I know how to sort these by whichever column I want to. But the problem is every cell needs to be the same size. And I have merged 3 cells in the first column and only merged 2 cells in the last two colums.
So Excel tells me I cannot merge unless each cell is the same size.
Is there a solution here? I need the 3 cells in the first column, so I have enough room to describe the transaction. And to avoid wasting space, I need to only have the other two columns be the size of two merged cells each.
Example, though with the text in another column:
Picture of it
2) The other problem is that I would like a cell to display the number in red if below zero, green if above zero. I cannot do this. I know where to put in the format codes, but I don't know what to write.
At the same time, I need the cell to show the currency in Danish Kroner. So I need a format code that does this. Someone told me this:
[Blue]dk #.##0;[Red]dk #.##0;[Green]*dk #.##0
But that doesn't work, nor if the color is in danish. What do I do?
View 11 Replies
View Related
Feb 7, 2014
I work with some stock index data and I would like to get rid of some dots and empty spaces etc. I used conditional formatting and changed the font color of all the values I need. When I try to sort them based on font color (in order to delete the empty spaces/dots) Excel does not do it. "Go to...conditional formatting" also doesn't work.
View 5 Replies
View Related
Oct 17, 2006
Is it possible to sort the excel records based on background or font color?
View 2 Replies
View Related
Sep 3, 2013
I was handed a pdf of some leads by a co-worker. Finally, I got the data into excel but it's in a pretty useless format... 30,000 rows of contact info with no rhyme or reason. I want to upload this into our CRM, but I need to get all the different bits of info into separate columns. I'm no excel pro, but I managed to use an Excel plugin called ASAP tools to bring some order to this chaos... All names have a blue cell color, all titles have a green cell color, work phone is red, and so on.
Does anyone know a way that I can sort these into separate columns? I've trying playing with the Filter function, but it hides rows which makes the output useless for my purposes.
View 1 Replies
View Related
Oct 27, 2009
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
View 9 Replies
View Related
Nov 18, 2007
I am looking for a very simple script that will achieve the following:
On the clicking of a button, Select and shade in a cell yellow, delete the yellow shading of the previous cell. The shading & selection should move up a column of cells, 1 at a time, in the following order:
From B10 to B9, then B9 to B8, B8 to B7 etc until the selection and shading is at B2. Once it is at B2 subsequent clicks will simply keep it at B2 (the top). Thus after 8 clicks the shading & selection should travel from B10 to B2, with only 1 cell being shaded yellow and selected at any one time.
View 5 Replies
View Related
Jun 18, 2008
I have a spreadsheet that i download from the net daily, which is seperated into columns of information.
I want to be able to look down a column and mark a cell in a seperate column if the cell font text is red.
For example looking down column A ... if the font text of a1 is red then mark the cell background colour of T1 red - if a2 text colour is red then mark the cell T2 red .... etc etc.
If the font colour in a1 or a2 ... etc etc is any other colour then do nothing.
I have 5 columns I wish to look down and mark in 5 seperate columns - I have tried to do this by conditional formating but don't know the fomula for checking font colour.
View 9 Replies
View Related
Oct 21, 2013
Im having problems trying to sort through data and linking certain data. I will post example below:
Main 1
Main 2
Main
3
Sublevel 1-4
[Code]....
This is just one part of a row of the workbook but shows the Main data which is made up of 4 different sub levels on the right. I merged 4 cells for the main data so that all the cells lined up but I'm still having problems with being able to sort and link the files together so that when I filter, they all do it together. The example didn't paste well.
View 3 Replies
View Related
Jun 15, 2008
in the the attached image of excel
i want to see what is the bigger number in the row 49
then sorting these numbers descending and write it in C52
then make a quick equation on in each number ,and this equation is to deduct this figure from each other nine numbers and see what is the zero or above zero result and put it the cell next to this number (column E) starting from E52
example
527.7 is the maximum number so i put it in the c52
and then deduct 527.7from each number ,like the following
41.0-527.7 = negative value (doesnot count)
21.8-527.7= negative value (doesnot count)
....
.... so on
and we will see that the only greater than zero or equal to zero result is only 1 result so we put the figure 1 in the cell E52
and so on on each number
View 11 Replies
View Related
Jan 10, 2008
I am using Excel 2003 and am after a macro that can sort a specific range of cells.
I have a spreadsheet that is used to record details of photos taken during bridge inspections.
•The details of the photos are recorded in column B
•The number of the digital photo is recorded in column C (the number of photos taken per inspection varies)
•After checking by someone else the photos are assigned a number in column A
After two blank rows that process is repeated for another inspection.
I am after a macro that when a cell within a range/block is selected it will sort columns A:C by the numerical order of the range block within column A.
For example when any cell is selected within the range A3:C7, that range and only that range will be sorted.
The same would happen when a cell is selected within range A11:C15, and so on....
View 9 Replies
View Related
Feb 7, 2013
I am using Excel 2010 and basically i am trying to fill a range of cell with a green color if any value was enter in a specific cells. Example: I would like to fill range: A10:c13 with a green color (regardless of the cells content in this range) if a value was entered in cell C10 or C11 or C12 or C13.
I've tried conditional formatting but unfortunately I'll have to apply formatting for every cell and for a range of over hundred cells is not efficient.
View 7 Replies
View Related
Apr 2, 2008
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
http://img530.imageshack.us/img530/6239/excelspfk0.jpg
View 9 Replies
View Related
Jun 8, 2009
I'm trying to do is sort cells which are referenced by names in the name box when a button is clicked. All the coding is in VBA.
If a cell is sorted by a macro, I would like the referenced name to move with it on the spreadsheet.
For example if the referenced name box for C5 is Bob and Andrew is the referenced name box in C6, when a button is clicked I would like the Andrew reference to move to C5 and Bob to move to C6. This is sorting in alphabetical order. However, I would like the referenced names to stay the same. Referenced Name refers to the cell name box.
Is there a way to do this in VBA?
View 12 Replies
View Related
Sep 21, 2013
I have this code to sort numbers from smallest to largest but i need it to ignore blank cells.
Sub sort1neg()
'
' sort1pos Macro
'
'
Range("a4:aq174").Select
ActiveWorkbook.Worksheets("sort").Sort.SortFields.Clear
[Code] ........
View 1 Replies
View Related
Mar 13, 2007
the system works by whoever has the most points wins.. i already have my formulas and everything done..
i want the excel program to move the entire row, if possible, by whoever has the most points to the top..
(i.e If ryan d. has the most points he will be is vince v.'s spot)
can this be done automatically in excel or do i have to do it by hand?
View 9 Replies
View Related
Sep 12, 2008
I have a worksheet with a data base type list (16 cols, 10000 rows deep) which contains only 30 records, for testing purposes. Each cell is bordered in all 4 sides.
There will be a bunch or reports associated with this, requiring filtering and/or sorting. The macro instructions I applied so far are:
1) Locate last row.
2) Select the range from first to last row and apply a common row height (say, 24).
3) Sort as required
4) Filter as required.
5) Print
The sorting step has invariably failed.
The funny thing is that the code I used is the one produced by recording the steps. I am able to sort that segment manually, but the macro instructions fail.
here is the code...
Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Key2:=Range("H5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
View 9 Replies
View Related
Jul 31, 2009
I am using Excel 2007.
I have a spreadsheet with about 30 items in 30 rows. I have a picture object which corresponds to the item # and other info in each row. When I sort the rows, the picture objects don't get sorted with the rows and end up no longer corresponding with the item # and other info.
I have right clicked and set the properties for each picture object to "move and size with cells". This does not prevent the problem.
View 9 Replies
View Related
Jun 29, 2006
I have two columns with the same data just totally different orders the third column (associated with the second) has data that I want to sort. I want to keep the order of the first, rearange the second so they match, and have the 3rd column follow the second to the proper location. i need to keep the order of column 1 so i can post into a massive spreadsheet. Theres gotta be a quick formula for this i just have no clue
View 2 Replies
View Related
Jan 31, 2013
I have a similar question regarding coloring of empty cells between two cells with specific numbers as posted in below thread : [URL] .....
This is exactly what I want. But here I have some problem with formula.
=COUNTIF(A1:Z1,1)=1
Above formula could pick cell value 1 and color the empty cells in a row between the cells having value=1. But if I want to use the same formatting for other numbers like 2 or 3 it doesn't work.
For example in row(A1:Z1) I have A1=1, D1=1, and F1=2, K1=2, and O1=3, U1=3
I required multiple conditional formatting that could color the cells based on values first from A to D then from F to K and finally from O to U.
I'm using above formatting it works with cells having values 1 but its not working for 2, 3 or any other cells values.
I have already tried replacing 1 in above formula with 2 and 3 but it doesn't work.
View 1 Replies
View Related
Jan 27, 2005
how can I automatically fill cells with certain colors based on the value of the cell. (i.e. I want to search an entire workbook and fill cells with values between 80 and 99 green, 60 to 79 yellow and 0 to 59 red.)
View 9 Replies
View Related
Jan 15, 2008
I’m trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. I’ve searched and found similar forums here, but since I’m ignorant in VBA code, I couldn’t manage to make adjustments. So if you can help me with the code, I would really appreciate it!
Condition and Results required would be:
IF column C = “S” than on Column B = color cell light blue with white border
IF column C = “P” than on Column B = color cell Green with white border
IF column C = “A” than on Column B = color cell Yellow with white border
IF column C = “L” than on Column B = color cell Red with white border
IF column C = “C” than on Column B = color cell Dark Blue with white border
IF column C = “ ” than on Column B = color cell white
IF column C = “V” and has white background than on Column B = color cell white
IF column C = “V” and has a Dark Grey background than on Column B = color cell Dark Grey
I don’t know if the last two are feasible.
View 6 Replies
View Related
Mar 8, 2008
I’m having problems linking data from various worksheets to one master worksheet, all in the same workbook. The workbook contains swimmers PB’s (personal best times) for each stroke and after each gala the swimmers new time is entered onto the worksheet. The worksheet data is then sorted so that the fastest swimmer is at the top.
The master worksheet contains all swimmers by name (alphabetically) and their best times for each stroke, what I am trying to do is have the master worksheet update automatically when I enter the swimmers new PB’s in the individual stroke worksheets, this works when I use ’Paste Special’, but when I go to sort the data with the fastest swimmer at the top, the data in the master worksheet does not reflect the correct times.
View 9 Replies
View Related
Oct 21, 2007
when I sort data columns that contain drop-down menus. My spreadsheet has several columns: "Patient name", "Room #", and "Phone #". Each cell within the "Phone #" column should have a drop-down validation list containing two possible entries: the room's designated telephone number, or NONE. Whenever I add a new patient to a room, these three columns need to be sorted alphabetically by patient name. But when I sort all three lists simultaneously, it looks great- but then I realize that the drop-down lists associated with each "Phone #" cell haven't sorted! They stay in their original location and don't sort with their cell. So the designated telephone numbers for each room are all screwed up.
View 9 Replies
View Related
Jul 1, 2014
I have some cells that are summed with total cost.... now i need to sort the totaled cells? How do i sort a cell that has a formula based on multiple other cells?
View 2 Replies
View Related
Oct 17, 2013
I downloaded a massive dataset in .csv format to work with. My first problem is that I'm having trouble sorting the dataset. When I open the .csv-file every bit of information comes up in the A-column instead of having INFORMATION1 in the A-column INFORMATION2 in the B-column and so on.
View 1 Replies
View Related
Nov 1, 2008
Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:
Date Name Craft Experience ...more info...
If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)
I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.
Example (Excel 2003): ..
View 4 Replies
View Related
Feb 5, 2013
I am trying to sort a list of business development leads. When a lead is dead I enter the date in the column entitled "Closed". Therefore, the Closed cell for active leads is blank. Whilst I still wish to retain the information of dead leads, when sorting I would like them listed at the bottom of the list. However, whenever I sort on the Closed column, whether using newest to oldest or oldest to newest, the blank cells are always at the bottom, instead of the top.
How to get the sorted blank cells to the top?
I'm using Excel 2011 on Mac OS X 10.7.
View 2 Replies
View Related
Apr 23, 2013
Here is the original table:
This is with a filter on:
You can see there that some inserted object (in this case, PDF files shown as icons), are moved. I need to find a way to immobilize every inserted object within each cell boundaries. I want to freely sort or filter and avoid this kind of problem that gets worse with more rows, columns and inserted objects.
View 1 Replies
View Related
