How do I change the color of a row, based on the users selection from a drop down box in that row? I have four driving types in my drop down , Towing - Town Driving - All highway - Mixed. I would like each driving type, if selected, to change the color of the entire Row of Data to a different color. I have been able to use conditional formating to change the drop down cell itself, but that is it.
View 1 RepliesI have a dynamic drop down list using OFFSET and want to change a selection of cell colors based on the drop down selection. My drop down list is located in cell G4 and the cells I want to change are
=$C$9,$C$10,$C$11,$B$11,$B$12,$C$12,$B$13,$C$13,$C$14,$C$15,$H$15,$H$14,$H$13.
These are random cells. There is no value in the cells, I just want to change the color from green to gray.
I am trying to use a userform ( Combo Box) to update my named cell "Aircraft1" then based on the value selected change the color of an object.
The code works great if I manually enter the value in the cell "Aircraft1", however if the dropdown list selects the value the object does not update its color.
Is there a way to update the cell via a combo box, and then have the VB code change the color of the object?
This is my
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Aircraft1")) Is Nothing Then Exit Sub
ActiveSheet.Shapes("Arrow1").Select
With Range("Aircraft1")
If .Value = 1 Then
ActiveSheet.Shapes("Arrow1").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
I'm using Excel 2007.
I have a field with a "YES"/"NO" drop-down box. There are three fields below which have some default formatting (thick purple dot-dot-dash borders). When the drop-down field is "YES", those three fields should change borders to a single thin black bottom border line.
OK... so that's the setup. And I should note here that it does in fact format properly. However, the problem I'm having is that the screen does not update / repaint with the new formatting. The thick purple dot-dot-dash borders remain, for the most part.
If I page down, then page back up, the new formatting appears just fine. When I switch from "YES" to "NO", again, the formatting changes, but the screen does not update/repaint so the only way to see the new formatting is to again... page up and then page down again.
I'm wondering if anyone knows a way to force the screen to repaint/update so that my users will see the new formatting without having to page up/down.
Im a fairly basic VBA user who has just purchased the code for J-Walk's Enhanced Data Form.
On other threads in this forum I have noticed a lot of people asking how to make the contents of one drop-down box dependant on the selection in another. I am trying to do the same thing but with the criteria section of the EDF form.
Is this possible? What does give me hope is that a simple Autofilter - whilst not as aesthetically pleasing, does the job well.
Currently I have columns fixed or assigned to the names of insurance providers/companies.
In each column below the name of the Insurance Company, a combobox containing the plans that company offers. Choose a plan, and the rest of the column is filled in using vlookup.
Now to save space, IE opposed to having 13-15 columns one assigned to each Insurance Company/Provider. I am looking to have only 3-5 columns with a first initial combobox in which one chooses the Name of the Company/Provider, and in turn populate the original combobox and vlookup functionality I already have built in.
Here is an example of what I am working with.
[url]
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
I have a drop down sub pasted to worksheet:
Private Sub ComboBox1_Change()
ComboBox1.List = Array(100, 200, 300, 400)
If Range("I11").Value < Range("N11").Value Then
If Sheets("Profile").Range("K18").Value < ComboBox1.Value Then
Range("I11").Interior.ColorIndex = 2
Else
Range("I11").Interior.ColorIndex = 3
End If
End If
End Sub
I want it to change the cell color on drop down change. How can I modify things to have the change in drop down selection?
I have two worksheets...
Sheet 1
A1: description
A2: Target Iteration
A3: Concat A1, A2.
Sheet 2.
A1: Drop down datavalidation list selected from A3, sheet 1.
On sheet 2, a user can select from the list. If a user changes the value in A2, sheet 1, I want the value that is associated and already selected in A1, sheet 2 to automatically update. There is a 1 to many relationship with the concat and the drop down. In that, sheet 2 can have multiple rows with the same value from sheet 1 A3. Is there a way when A3 sheet 1 changes, to search in A:A in sheet two and update the values for those records that match the original value in A3, sheet 1?
I have a chart where half the users want a black backgroud and half want a gray background. Is there a way I can change the chart colors based on a textbox value? I would have the user choose a value (say, "Dark Background or Light Background") and then, upon commencing the macro it changes the chart.
View 4 Replies View RelatedIs it possible to change those AutoFilter drop-down arrows that are active? As it is now they are hard to spot which is in use.
View 5 Replies View RelatedI just loaded 2007, and among the things driving me nuts is I can't figure out how to change the color of highlighted cells that I have selected. The default color is so close to the normal background color(White) that it is difficult for me to distinguish between selected cells and unselected cells.
I've tried the built in help but couldn't seem to search for the right phrase to bring up a relevant answer. I've had the same problem when trying google and Microsoft's office 2007 help site. I also searched the forum before posting and came up blank.
I have a dashboard that uses three pivots based off of a single data source. Each of the pivots is designed to work off of their own drop down list.
the first drop down list allows the user to select a lease date. The associated pivot will update with all servers assocated with that lease date.
the second drop down list allows the user to select a server. This list is limited by the lease date selected in the first drop down. The associated pivot will update with the partition information for the server selected in the drop down.
the third drop down list allows the user to select a partition. This list is limited by the server selected in the second drop down. The associated pivot will update with the detailed partition data for the partition selected in the drop down.
All of the above works, the issue is that when a new lease date is selected in the first drop down list, the current entries for the second and third drop down list are still reflecting the old lease date information. When you click the drop down the correct data is there, it is just not automatically populated.
Is there a way that when the user selects a new lease date in drop down one, the first server in the new list is automatically populated in the second drop down as well as the first partition in the third drop down?
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
View 9 Replies View RelatedThere's a table with all the raw data in it
camp name
session name
to put in the correct format I am thinking of usinf drop down boxes so that the user can selct the camp name from drop down and based on that the session name can be populated and alos the rest of infomration of that accput in to the specified fields
how to get a 2nd drop down box to update the range (Not just use C6:C7)based on selection in 1st drop down box.
Example:
If Fat, Use range from C6:C7
If Skinny, Use range from D6:D7
Note: I tried putting an if statement in the input range for box2 but won't accept it.
I have a workbook consisting of 5 sheets.
Sheet 1 is the master sheet, and (among many other columns) it has a series of yes/no columns. The yes/no options are the equivalent of:
"Are you in sheet 2?"
"Are you in sheet 3?"
etc.
When this is the Yes option, I would like the rest of the contents of the row to copy into sheet 2.
These need to remain linked, so if I change stuff in Sheet 1 I would like it to change everywhere.
I have a pricing model I'm working on where the request is to have all of the line/part items in the model, but only display a subset of those items based upon a sub-line chosen by the user by a drop box that includes all sub-lines.
Right now the list of parts is a table that I'm querying that also includes a simple '1' or '0' to indicate whether every part is an element of one or many sub-lines; eg (sorry, sure this won't format right):
Item SubA SubB SubC
210-1010 1 1 0
210-1011 1 0 1
So if choosing SubB in the drop box, I need the autofilter to show the first part, but not the second one. I think I have to do it this way (autofilter) because it will retain all of the parts whether the user is looking at them at this moment or not; they will use this tool to perhaps first price all SubB items, *then* go select SubC and price all of *those* items - and it needs to retain everything they've done, rather than a dynamic query that would lose prior changes and pull in a whole new set of data (if that makes sense).
So.... I need my autofilter to react not to specifically choosing data on the autofilter itself, but to a choice made in the drop box (probably a macro/VB attached to the box that reacts to the linked cell value that results from the choice, but I don't know how to tell it if the linked cell value is '5', go to column 7 (first two are part number and description) in the autofilter and filter on that column.
Attached is the file i am working with. The table spanning from A:AA5:16 contains hours worked per day of the week with respect to the Project chosen from the drop down menus in A7:16. Cells in the range of F,K,O,R,U,X,AA7:16 total those hours worked on a daily basis. The table below should sum the total hours worked per day according to the Project selected in A19 from the drop down menu, with respect to the Project(s) chosen in cell range A7:16. For example, if i am looking for total hours worked Monday on Project RSB i select "RSB" from the drop down menu in cell A19, i want cell F18:19 to be the sum of cells A7 and A9, with respect to the Project selected from cells A7:16. If I select "FRG" from the drop down in cell A19, i want cell F18:19 to reflect the sum of cells F8 and F10.
View 2 Replies View RelatedI have created a pricing sensitivity for a list of products. In cell C3, users can select a product (i.e. Apple, Elmo, or Bowl) and based on this selection, a range of Prices and Units are then updated and the Revenue for each price point calculated. A few lines beneath this, I have a little summary table that lists all the products. I'd like for users to be able to enter in the optimal price point for each item and then have the Units and Revenue for that selected price point for that specific product automatically update.
For example, if I select Apple from my drop down list my price choices are $5, $10, $15; units are 100, 85, 70; and revenue are $500, $850, and $1,050 respectively. On the summary table, I would like units 85, revenue $850 to update automatically if I select/input a $10 price. This would happen for all the products so at the end I want to be able to calculate a blended revenue mix given my pricing changes.
I need VBA code to do the following:
When cell H2 = "Weekly", I need cell I2 to return "50".
When cell H2 = "Bi-weekly", I need cell I2 to return "25".
The value in Column I should change as soon as the value in Column H changes.
NOTE: The value in Column H is chosen from a Data Validation List.
I'm assuming offset is the best option?
I have two columns. The first one (A) contains cells that have different Fill colors. The second column (B) contains text adjacent to the colored cells. I am trying to change the color of the text in the second column (B) to the corresponding color in the adjacent cell in the first column (A). I don't think conditional formating works well in this situation. I believe the solution would be some sort of macro.
View 3 Replies View RelatedI am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies View RelatedI'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.
i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.
Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
I have various row cells in column (F) filled with the color Green. And corresponding text in Column G. How can I change the text of that particular row to white.
i.e.: if any cell in column F is Green, change the text color of that row in Column G to white?
I am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
I've tried to do conditional formatting for each of the "Discipline" values in this spreadsheet but it is restricted to only 3. Is there a way around this? I'm trying to setup a color code for the "Discipline" values for example RN=Red, LPN=Blue, Social Worker=Orange, Chaplain=Purple, STNA=Yellow, Volunteer=Pink & Other=Green. So when the "Discipline" cell/cells on each of the patient sheets (ie. "Doe, Jane", "Doe, John", etc.) is equal to say RN the cell would then turn Red either on the spreadsheet or only when sheet is printed.
View 5 Replies View RelatedI am new to VB macros, I am trying to figure out how to color a tab Green based on a selection from a drop down list.
View 9 Replies View RelatedI have two drop down menus: Class Name and Class Period.
Class name returns reading, math, science, or social studies.
Class period returns 1,2,3, 4,5
I need a formula for a cell that will return the name of the first student in whatever criteria is selected by the two drop down menus.
Example: If reading 2 were selected, I would want the name of the first student in reading period 2.
I did start out with nested If's combined with ands, but that is too many nested ifs. I figured there was a better or easier way to do this.
Formula I thought of: =IF(AND(G3="Reading", H3=1), '[Reading 8.xls]Sheet1'!$A6, IF(AND(G3="Reading", H3=2), '[Reading 8.xls]Sheet1'!$D6)) etc.....
But if I use that there would have to be 20 nested ifs, which I think I read someone nested ifs cap at 9.
I have created a named range that includes raw data layed out in 4 columns (Finish Name, Fill Color, Pattern, Font Color). On a separate sheet, when one of the finish names is selected in a particular cell, I want the cells fill, pattern, and font color to change according to the raw data. Below is the vba code I am trying to get to work. However, it is not working. It does not break, but does not achieve the desired result. It looks like the variables are getting set correctly, but still not changing the cell when they are referenced. I am only testing it on the first finish color, as seen in the code. All of the rest are static values for the cell, but I want the values to be dynamic.
Sub SetFinishSeparatorColor()
ActiveCell = frmFinishSeparator.cboFinish.Value
Set rngTarget = ActiveCell
With Sheets("Pick List Do Not Tamper").Range("Finish_Cell_Color")
lngFinishCellColor = . Find(What:=rngTarget, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1)..............