I'm looking to auto populate "K7" with a color to indicate project status (i.e. green, yellow, & red) based on a percentage in "K1". The tricky part for me is I want a date range in G58 & G59 to drive the color in K7. So, when I enter a percentage in K1 and (for example I'm not at 90% with 3 days left in the project K7 will be RED.
View 3 Replies
I have a column with (potentially) multiple values in, heres an example:
Marketing Specialism
Commercial, Multi-channel, Loyalty/Retention
Analytics, Partner
Analytics
Commercial, Analytics, Segment
[code]....
What I need to be able to do is count (and then chart) the number of each value, irrelevant of whether it appears with another value in a cell. e.g.
Analytics, Parter - Would count 1 Analytics and 1 Partner
Analytics - Would count 1 Analytics
I know there is a formula where I can specify the value to count, but as the column has so many different values I would have to write a formula with each separate value, this would take a long time.
I have multiple rows within a cell separated by Alt+Enter, and would like to combine them as follows:
Desired Result
First
First
Name
Name
First Name
First Name
My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...
Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..
When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of
Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x
I had
Code:
Combobox1.list = x
And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).
I have a worksheet set up where one column of data (we shall call it "A") is the result of several other columns' calculations (uses a formula referencing other cells). I have an entirely separate cell that also gets its data from other cells (we shall call this "B"). What I'm trying to do is make the shading of column "A" dependent upon data from column "B". For example, if column "B's" value is >24, I want column "A" to shade in red.
View 10 Replies View RelatedI 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.
I have a spreadsheet which needs formatting I was wondering if anyone would be able to help with creating a macro to do so. The problem with raw report is that in cell A47 there are five columns worth of data in that one cell, then in B48 there are another two, in b49 and b50 one respectively. I manually format it by first joining the separate cells using
View 11 Replies View RelatedI am populating a ComboBox with entries from a Range. The entries have a different Interior.Color depending on whether the individual project represented by the entry is complete. If the entry is complete is has an Interior.Color of blue; if not, it is red. I want to populate the ComboBox with both, but if the Interior.Color is blue, I want to change the font.color displayed in the ComboBox to blue. Just the color of the completed (blue) entries. The other (red) entries can have a normal (black) font. Is there a way to do this?
Here is the code I have been working with, but nothing I have tried has worked. Maybe another strategy, or whatever.
LastRowTF = Sheets("Shipsets").Range("F65536").End(xlUp).Row
Set myArray1 = Sheets("Shipsets").Range("F2:F" & LastRowTF)
For mA1 = 1 To myArray1.Count
If myArray1(mA1).Interior.Color = 255 Then
[Code]...
The group behaves as one shape alright when, say, coloring the line, but is not "fillable". Below a simple sample. - The custom shape i am trying to color in is not a simple square or rectangle -not anything from the existing MS shapes that is....
View 6 Replies View RelatedI am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?
View 3 Replies View RelatedI am trying to find a way to write a Macro that will auto color multiple cells based on what data is displayed in one cell. The cell I want to reference is a vlookup cell.
Basically this is a part label. And depending what part is selected from the list my vlookup will display its position on a vehicle(i.e.. FR, FL, RR, RL, Etc..). So if vlookup comes back with FR I want the various cells on the label to be orange, etc..
ALSO: if there is a way to embed it so it does this automatically (rather than run the macro each time).
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.
I have a long chain of formulas calculating the return on a particular investment in equipment. We'll call the inputs A, B, and C, and the output (total savings generated) Z. There are several set combinations of values for A, B, and C that I'm interested in generating a summary report for.
There are a number of steps in-between the inputs that I'm interested in, all of which are necessary to get to the output, but is there a good way to have excel chart inputs and outputs without filling out the intervening data?
I suppose the whole thing is essentially a multi-variable, multi-step data table.
I am using Excel 2010.
At work, we've got a program that outputs the results of a search into an Excel file, in column 1 below.
17,43,61,63
17
43
61
63
23,29,53,57,77,79
23
29
53
57
77
79
17,29,63,69,71,75,79
17
29
63
69
71
75
79
11,43
11
43
57
57
I need to get that list of numbers listed out to the right, with one number per cell. The list in column one could possibly contain from 1 to 20 numbers, and the last number is always without the comma after it.
if it actually possible to colour data points depending on the colour of the cell of the data it refers to? if not is it possible to colour it any other way
i have uneven amounts of data for weach month but still want the months to be displayed, this is the best way i can think of of doing this
I have a Listbox (Listbox2) in column C. The listbox allows users to select multiple items (in this case it is countries). So the user could select 1 or 20 countries based on the previous criteria.
What I would ideally like is for the next column (column D) to automatically list the selected items without having to use a command button in the actualy cell. However, I could put a command button at the end to say "Confirm" which would then transfer the above into the relevant cells.
This is an excel file, and each row would works the same. Basically users are entering products for set up row by row, so the file could be 10 rows or 200.
Just to be clear, it would look a little like this:
Drop Down Listbox
Column C Column D
Canada (Selected) Canada, USA
South Africa
USA (Selected)
I am trying to come up with the most efficient way to copy data to multiple sheets within the same Excel workbook. The original data exists within one column on a summary sheet (could have up to 500 individual entries). I want to copy each individual entry to a unique sheet (that already exists), but in the exact same cell location within each sheet. I would only want to copy the original data value and not any formatting. Is there an efficient way to do this?
In my example spreadsheet, the original data is on the SUMMARY sheet. Sheets A through J would be the target sheets, with cell B2 as the target location for each of those sheets. My example shows the result of a manual copy paste value process, but I am hoping to automate that.
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
I need to create a comma delimited list based on variable start and end values for each row.
StartEndOutput List
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200420072004, 2005, 2006, 2007
200420082004, 2005, 2006, 2007, 2008
200520082005, 2006, 2007, 2008
200620082006, 2007, 2008
200820082008
I'm not a VBA expert, or I would have created a Do While or For Each loop.
I have a spreadsheet with multiple columns, column N contains different countries (some are blank). I want to copy all data in that row (except columnt H) to a new sheet if N = USA.
Macros are disabled so a formula(s) is needed.
is it possible using excel 2002 to hide individual cells instead of whole columns or lines
View 9 Replies View RelatedI am running a loop that changes the value in one cell at a time. Since this is for our entire company, I am trying to make it "idiot-proof" like most of you have done.
While in the loop, I want to unprotect a cell, change the value, then reprotect the cell. But the rest of the worksheet is going to be unprotected for now. is that possible? Or do I have to protect the whole sheet, and then .Unprotect the whole sheet first and then. Protect it at the end of the macro.
I, daily get a list of individuals with some data against each one of them. E.g Amit would appear 7 times in the list, John would appear 10 times in the list and so on and so forth. I am required to sort the data as per names and then fill one background color. One color for one person so that it becomes easy to read data specific to an individual.Ia there a way that the color fills up automatically. note the names and number of entries are not same everyday.
View 8 Replies View Relatedhow to do basic formulas in Excel but I are hoping that there is a macro or a VBA or something that can be made which might work for what i need.
I have decided to start a small tour shop (I make the reservations for guests with a hotel in the area and take them on tours of our region) and are looking to make a booking sheet for each month. Nothing complicated just simple. see the attached .xlsx file.
I was wondering if there is a way that when the fields in column A & B are filled in if it could automatically color the corresponding dates in for the same row in the calendar area? like I have manually done on sheet "October 2013"
[URL]
I am working on a code in which i have to change the color of certain cells if the value of a cell is Y/N. The color should change with the user input.
Set wsheet = wbook.Worksheets("XYZ")
For Row_start=10 to Row_count 'Row count is the last used row in the worksheet
RowValue = ActiveCell.Row
colValue = ActiveCell.Column
if colValue = 17
[Code] ....
I am trying to color cells with a special value in another cell.
I would like to use the value in K column to sort the color of the rows. As you can see I have done this manually there, but I would like to have it automatic so no one will put wrong color.
I tried to do this in VBE and started to put K7:k37 as range. then I used if formula to find if the correct value is in the cell. But then I had know idea what i was doing so I did some search here but could not find anything that I could use.
Is it possible to add text to individual cells which can be written over?
e.g. I would like A1 to have the background text of "slot 1"... Once user has inserted text then "slot 1" is removed... and when the user text is removed, "slot 1" reappears.
What I need to do is hide the value in an individual cell, dependent on whether a value (any value) has been entered in another cell. I know this must be possible somewhere in conditional formatting but I can't seem to figure it out no matter how much I try!
The table below should hopefully explain exactly what I'm after. I want to hide the value in the balance column (automatically calculated)when no transaction has taken place (ie, there is no date entered in the date column). At the moment this value appears all the time.
DATE
DETAILS
DEBIT
CREDIT
[Code].....
how to change an argument in VB to range to each and every individual cell in the range?
At the moment i have :
If 100 < Application.Min(Range("G5:O5")) Then
but i want to change it to :
If 100 < Application.Min(Range("G5 or H5 or I5 or J5 or K5, etc).
I think that "or" is the correct way to describe it.
I guess i mean that i want the formula to look at each individual cell instead of the above code.
I would like to be able to write an article with data extracted from an Excel workbook. An understandable example would be like the books for kids which are personalized.
For example, using this data:
Name Friend1 Friend2 Town State Pet1 Pet2
Karen John Harrold Portland Oregon Snippy Goldie
Luke Mike Mark Solon Maine Charlie Max
I would like to write to a text file, vba text box, something like:
One day, B2 went down to B3's house to play a game. B3's cousin, C3 was visiting from D3, D4.
Which would output:
One day, Karen went down to Harrold's house to play a game. Harrold's cousin, Mark was visiting from Solon, Maine.
The text can be simple, no font formatting.
The data is coming from different sheets in the same workbook.
The data includes text, dates, and numbers.
