I have a sheet with alternating colors (gray,white) for the rows. This makes it easier to read each individual rows data, just like some printer papers. The problem i have is when i sort the data it takes the background color with it and i end up with a mess. does anyone know how to get around this.
View 2 RepliesWithin the Workbook events there is a "Before_Print", however I also need something like "After_Print" which does not exist (as far as I can tell).
I have several rows of data which alternate colors. When I print it I want to take out the fill (so as to save ink) - which I can do in the "Before_Print" event. [No problem.] However, once it is done printing I need to restore the alternating colors, but I cannot think of any event or trigger which would enable this.
I have thought through several things and the closest thing I can think of is setting up a timer. But there is of course no way to now how long to set it for. [Before/After Save also has the same "issue".]
The following code works fine if the data is sorted from oldest to newest. I need for it to work when the data is sorted from newest to oldest. For this to work, I would need a change that works something like this: "total1 = total1 + close_(a, 1) * a" would be "total1 = total1 + close_(a, 1) * b" with "b" incremented in the opposite direction of "a". I code very infrequently & have tried a lot of different things, searched the Internet, etc. all to no avail.
For instance, with "close" data in column "I", the data sorted as follows works with the formula
"=WMA(OFFSET(I2,0,0,9,1) )" to correctly return "791.50", but the opposite sort returns the wrong number.
The correct calculation is: (807.19*9+798.38*8+793.81*7+793.06*6+776.28*5+769.48*4+773.2*3+789.01*2+793.87*1)/(9+8+7+6+5+4+3+2+1).
DateClose
11/12/2012793.87
11/13/2012789.01
11/14/2012773.2
11/15/2012769.48
11/16/2012776.28
11/19/2012793.06
11/20/2012793.81
11/21/2012798.38
11/23/2012807.18
Code:
Public Function WMA(close_)
total1 = 0
n = WorksheetFunction.Count(close_)
divisor = (n * (n + 1)) / 2
[Code] ..........
My pallet lost color-coding - if I hover over each little scare it displays the names for the colors and if I click on them they color the cells with the right colors, but the palette itself lost the visual display of colors except for 8 colors: black, blue, red, magenta, yellow, cyan, and white.
I use color-coding of cells a lot and I find it difficult to work without visual clues. At least the hover-support allows me to get the work done, but with difficulty.
I have 3 columns Z, AA, AB. The heading for Z1 is “A”, AA1 is “B” and AB1 is “C”
In column Z2:Z2000, there is a mix of A, B’s and C’s. I want A to stay in Z1 column, B’s to goto AA1 and C’s to goto AB1, also I want this added to a macro that I previously created, so everything happens with one push of a button
Now for save, not sure if this is possible or not, if I can have this added to the macro as well that would be great. When I push my macro button, the file saves to “Dec (today’s date) DB (81).xls” The number 81 is the total count of A, B’s C’s, this # will change depending on how may A, B’s and C’s there are. I really hope there is a way of doing all this
BEFORE
A B C
B
A
A
B
C
C
C
A
A
B
C
SHOULD LOOK LIKE THIS
AFTER
A----B-----C
----- B.....
Essentially, there are two columns I am dealing with. One is "Sales Rep" and it lists all of the sales reps employed by the company. The other is "Zip Code" and that will list all of the zip codes that sales rep is responsible for.
Now, I have a row of data, all of those zip codes listed out, that each rep is responsible for, but my supervisor wants all of the zip codes listed in one cell, in that second column. Example: (02018, 34098, 16711).
The commas are not necessary, but is there any way to get this done other than manually entering them?
I have cut and pasted a large amount of data onto a spreadsheet. The problem is that I have two rows of data that is associated with one another, so lets say A1 has a name and A2 contains that person's age. This process repeats in the same way in A3 and A4 and so on.
I need to move the data from A2 to B1 without using a simple function like =A2 in B1. The reason is that I will need to sort just the Names in column A once I get the the data moved. I am not sure how to do this.
The attachment below is a basic example of what I am trying to accomplish. I am looking for VBA code to take data from E3 in "Historical1" sheets and move it to down into the table in column E that corresponds to the date in D3. The other "Historical" sheets will work the same way. Cells D3 and E3 in each of the "Historical" sheets equal back to cell on the "Entry" sheet. This is a very basic example of the ultimately bigger data base I am building. why I am utilizing a entry sheet to elevate the need to go to each sheet and enter data.
View 2 Replies View RelatedThis is the last task in a project I inherited. I have included a sample sheet with 50 rows of data which represents a real sheet with 185,000 rows. This sheet has seven columns, but only two of which apply to this task, columns A and B. Column A is a list of parcel numbers which can, and in many times does, repeat multiple times. Column B is a list of the 'sellers' name for the respective parcel number. This sheet has been sorted by parcel number, then date.
As the current 'seller' was the previous 'buyer', I need to move the 'sellers' name down one row, if the parcel number (Column A) is repeated. If the parcel number is not repeated, the name (Column B) will need to be deleted. This will leave the 'top' line for each unique parcel number blank, which I can work with. As far as the bottom name for each unique parcel number, it will need to also be deleted as there is no where for it to be transferred to. Only the name will be deleted from the 'bottom' row, not the rest of the data. The end result should not change the amount of rows of data.
I need to move data from columns B & C into A without losing current data in column A (see attachment). I'm sure I know how to do this but cannot for the life of me remember how.
View 5 Replies View Related I have a Rental Form that i'm working on, and to tidy things up when I print it out, I would like to move a cells data down 1 row if that cell is empty. Here's what I have:
cell b8 - First and Last Name
cell b9 - Address1
cell B10 - Address2
cell b11 - City, State, Zip
cell 12 - Phone #
etc.
Not too many people have an address2, so when that field is empty, I'd like to move Address1's data down to it's position (address2). If address2 has data in it, leave address1 where it is. Simple right? This moving would happen when the command button is hit and the form data goes to sheet 1 which works fine.
in writing a loop that will check a number of cells to see if it is emtpy, if it is not, then run the macro. If the cells are not empty it will copy the data in that row and paste it to another sheet and delete that line. If it hits a cell that is empty, i want it to skip that row and move on to the next row.
here is the macro that moves the data.
Sub movedata()
Range("A1:H1").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Sheet2").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
Sheets("Sheet1").Select
End Sub
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
I have a big set of data and I want to create different graphs in different tabs with it.
So Imagine that in Tab 1 I want to do the sales forecast for the next five years for product A for all countries in Europe
Tab 2 would be the same, but for product b
Tab 3 the same, but for product c
So with dynamic tables in each tab I can easily do my graph
The problems is that since values vary per product country A in tab 1 is red, in tab 2 is blue and in tab 3 may not exist.
How can I set up a template so taht I do not have to color code each country in each tab? I.e. I want Spain to be blue in all my graphs in the workbook, Italy red and so on...
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
I would like to be able to sort the data in this one cell alphabetically without retyping the data .ie use a sort function but only for a cell.
For Example cell A1 contains Chris Brown Andy
No commas there. Names are seperated only by spaces.
What function should I use in order to get Andy Brown Chris ?
i have a file which consist of sheets called june 2007, color, and report. When i click on the command button on sheet june 2007 the code works and copies some data into sheet called report in that case some of them white color copied over and some of them blue.
Since at the actual file there are nearly 500 rows and 32 columns when the report works, i want to sort each colors individually by looking the values at column called ETA (column "E")
white rows should be sorted in ascending order in column e wise
blue rows also should be sorted in ascending order in column e wise
each colors should be sorted individually
How can i link colors used in charts to companies i have i my database. Now excel provides colors in a certain sequence but this is not what i want. I attached a file in which i for example wants to create pie charts of the company's sales. What do i have to do to give each company its own color?
View 2 Replies View RelatedI am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
I'm trying to create a formula that can display colors based on the strength of the margin of victory of a candidate, take these two examples for instance.
County
Frank
Cole
Dean
Total
MOV
MOV%
[Code]....
I want to be able to draw in the outline of these counties (or at least the cell proceeding the MOV%) displaying the color that corresponds with the winner of that county. The added twist would be to shade the color depending on the Margin of Victory (MOV) as well. The closer the vote, the lighter (or mixed?) the cell (or county) would be. If it could be at all possible I would really be cool to mix both colors so that a result 100% for one candidate would get a color 100% pure while a 17% MOV would get a color 17% more of the winning candidate than of the loser (or losers) colors. I don't think a VBA is necessary for this one.
in a data validation list can we show colors,assume the following list as a data validation list
Item #
Orange =50
Apple =60
Banana=56
Mango = 40
in this list i want to give colors (as shown) to these two items
I have a long data in a single column with mixed letters. and column A (or H, K etc.) like this:
e1.JPG
How can i sort that cells to alphabetically like this?
e2.JPG
I already tried a function discussed this topic, but it's not working: [URL] .....
I have a softball spreadsheet with columns of ab, r, h, 2b, 3b, hr, bb, so, Avg. rows of all the players on the team. I'd like to be able to click on the cell containing any cloumn heading and have the data sort out from best ot worst of that catagory. Is it possible to re-sort data simply by clicking on a certain cell, and if so can it be done for 8 different cells?
View 9 Replies View RelatedTrying to format cells b9 through b 24 and cells c9 through c24 to change from white to a light grey color by clicking the mouse button once. so one click white, another click grey. The color grey I need is white, background 1, darker 25% in the autofill field in excel. I have search other forums they all lead to changing the code, I did this already. just copy pasted a code I found but all cells were selected to change a bright green.
View 3 Replies View RelatedI am making some grids which will have a series of numbers in the individual cells. But I can't figure out how to change the colors of the digits. If I change one digit to Red; all the numbers in that cell change to Red. Whereas I would like to have them as Red,Green,Black,Blue,etc.
View 3 Replies View RelatedI'm trying to update a workbook with a new look and want to change the cell color based upon what the cell color currently is.
I'm having problems with the code that I created. I just can't seem to get the syntax correct for the code.
For the code below, my Range of A5:D5 is just a test range where each cell should change colors. When completed, my range will be much greater.
Code:
Sub ChangeColors()
Dim Cell As Range
For Each Cell In Range("A5:D5")
[Code]....
I have read the instructions for conditional formatting and I don't think that is what I need. I have 10 diff year 2005,06,07 etc. I want each cell with with a diff year to turn a diff color. Like I want 2005 to be red and 2008 to be blue.
I'm trying to have change if the date on it is passed "now()". I am already using the 3 conditional formatting fields and need this one and another one. Here are 2 problems I seem to be having.
First - the code below only works if I change the date on the cell. I want code to either work with Worksheet Active or any other way so the user does not have to redo the date’s everyday.
Second - a record might have conditional formatting already. Is it possible for the target cell in this code can show this color while the rest of the row shows the color of the conditional formatting?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("G1:G2000")) Is Nothing Then
Select Case Target
Case Is <= Now()
icolor = 39
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
I created an excel workbook with multiple cell reference throughout the various worksheets. I did this because I was creating a dependent drop down list. When I try to sort my data alphabetically, all my cell name references get changed. How I can sort alphabetically without losing my cell name references?
View 7 Replies View RelatedI need to change the color of a specific cell when conditions are met in a different cell and it seems like it may be beyond the scope of simply applying conditional formatting. What vba code would look like in proper syntax to make the following statement true. IF cell value Q4 is >=cell value S4 AND cell value R4 is <100 Then cell Q4 turns red and IF cell value Q4 is >= cell value S4 AND cell value R4 =100 then cell Q4 turns green. it may also be useful to know that the "cell values" in this example will be dates and not straight values. this may be possible to do using conditional formatting, but i cannot find a formula that works.
View 2 Replies View Related