I am trying to change the color of a cell to match a selection from a drop down data validation list. There are quite a few "color cells if ......" VBA codes out there, however they don't quite do what I need and I am not experienced enough to figure it out.
The data validation references a named range (list of consultants) on another worksheet within the same workbook. The data validation list needs to be dynamic because people get added or subtracted from the list. So the named range is actually equal to (A1:A100).
I am using Excel 2003, therefore conditional formatting is too limiting. Each company has it's own color. So I defined each company as a named range as well for my VBA code. For example, named range "AR" equals list Architect A, Architect B, Architect C etc.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error Goto 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
This code works for the first name (Architect A) in the named range "=AR". However, when I choose the next name on the drop down list the color in the cell goes away. Also the other named ranges do not change color at all. I am attaching jpegs of the named range and the sheet they are referencing to give an idea of what I would like the sheet to look like.
Add Workbooks only, not silly PDF files with pictures!
I want to configure a cell in Excel for color validation and I don't know how to do it. What I want is to configure a cell with a drop-down list of colors only, so the user can select from a set of predefined colors. I know how to create a data validation list by placing characters in a verticle list of cells, naming the grouping, then use data validation using a list to provide user selection. I also know how to conditionally format the cell based on the value chosen from the list, e.g. if 'R' is chosen from the list, the cell color changes to red, and the 'R' from the named list is placed in the cell. What I want to do is choose from a list of colors and only place the color in the cell.
I tried using non-printing ASCII characters in the list but Excel does not accept them. I tried to color the cells that comprise the drop down list, but the colors do not show up in the drop down list, nor when a member of the list is chosen. All I want to do is have the user select from a drop down list of colors only.
First, I have read through the board, and I was not able to find a solution already posted. I apologize if in fact this is a duplicate.
Second, I am comfortable with dynamic lists, dynamic ranges, chained validation lists, etc: so I am pretty sure my question is not a repeat of the other recent validation list questions.
Therefore:
Is it possible to generate a validation list from a single cell with a comma separated list?
For example, if I have “Fresh, Cool, Hot, Neutral, Smooth” in given cell, can I then have those 5 items appear as separate choices in a validation list?
I have tried every combination I could think of using arrays, named lists, the INDIRECT function, etc. I cannot seem to get it to recognize them as separate items. For the moment, I have created a bad (too long/complex) workaround where formulas are used to pull out each of the 5 strings into individual cells.
I'm trying to have a Data Validation list issue where I want to populate a second list based upon the value of the first list.
In this instance I have a Department in the drop down and Employee Name in the second drop down and I want people to only see Employee names if they're allocated to a particular Department. I've been able to do the list using the INDIRECT function, however that only works when there's only a small sample of both names & depts, however I'm looking at populating this document with around 3000 people and over 250 departments, so I would like to be able to use Column J on the Source Tab to populate the details on the Manager Entry tab.
I have attached a sample spreadsheet if I've not explained very clearly.
I have a cell that has a validation list. When i select a value from the list, i want the value of another cell to change automatically but it isnt working. The list source is pointing to another sheet:
=INDIRECT("DB_DAT!$J$268:$J$275")
I get an error when it tries to change the cell value. Its error no. 1004.
In sheet 1, there are two columns, Category and Subgroup. The cell validation for Category is pulled from the category list on sheet 2. Basically, what I want to happen is depending on what is selected as the category, only the related sub-groups would be populated in the sub-group validation dropdown list. In other words, if Fruit is selected as the category, I only want Apple, Orange, Pear, Banana, etc to be in the validation drop down list for the sub group. If vegetable, then only Corn, Green Bean, Cucumber, and Peas should be shown.
I've included two options for the relationship list as I'm not tied to either style and not sure which would be easier to work with.
Is there a way to have a formula or a VBA code associated to the choice of any value in a Validation list.
For example is there a way to have lets say in A1 a validation list and to have B2 returning the username of the person of who will select a value in A1?
I am trying to get a list of values for cells that have been colored.
The following is the macro that I have but I am not an expert on VBA
I think I need two things to achieve this.
1. Would really appreciate if someone would please help me debug this?
2. After I get this list of values I would like to be able to apply this to a worksheet and in one column define the differing row colors by a value. What would be method to do this?
Sub PrintRGBValues()
Dim intNumColor As Integer Dim strHexVal, strRGBVal As String
I have a table. I want to apply conditional formatting to the entire table so that wherever a cell contains a dropdown list (validation list) the cell is formatted with a different colour.
I have a workbook with 4 drop down lists. The selection in list 1 determines what is shown in list 2. I wish to have list 3 dependant on list 1 also, but don't know how to associate 2 drop down lists with the same previous cell.
The first list is in cell C2 and the table of data needed for list 3 is AC8:AC27-AO8:AO27,including the column titles.
I am including a sample. On sheet 1 I am trying to do data validation in that large merged cell using List and =Cements. Why do I get the error that pops-up?
How to have a validation list (drop-down) from a single-cell in which values are separated by a comma.
Example I have.
I have a wine list which have many categories of which
COL A / COL B / COL C Wine Name / Wine Producer / Vintage
I wrote a VBA code to look-up duplicates names and give the producers that make them. The names of the producers will be in an invis cell as Comma Separated Values.
For example: Wine 11 is made by producer X and Wine 11 is made by producer Y. I will have X,Y in a cell which need to be in a drop-down menu when Wine 11 is selected.
I got everything done. The only issue is to get the list. The problem is that the drop-down list shows X,Y as a single component and not as 2 drop-down components. If i were to write down X,Y in the validation list tab, it will show it as 2 components but referring to the cell doesn't.
I have a worksheet with 3 particular columns (L,M,N) that have drop down menus. The menu is populated with items that are referenced on a different workbook (different file). Because there are multiple worksheets accessing the referenced list, this is a master. When I change the master, the lists update, but the cell text does not. Example:
If I chose apple from the list, but later in the master I decide I want apple to be banana, while the drop dowm box will now have banana instead of apple, my cell still says apple.
I don't necessarily want apple to change to banana, but I want a visual cue that this is incorrect. I wanted the cell text to change to red if it is not equal to any of the list items on the master file. I want this to be passive, i.e., I want it to change automatically, so I don't have to press a button or some other activation.
Column B contains dropdown list with 7 items in it. When an item is selected from the dropdown list I would like to highlight cell in the column A of the same row. Different color needs to be highlighted for every items.
In cell H5 there is list of dates and in H7 there is codex, based on these two conditions in H11 a data validation list should be populated from the code_sheet using column B. The populated list should be unique entries, as the column B in code_sheet has duplicates.
I have a validation list drop down box set up to reference data in a defined name list. I would like to be able to select multiple values from this list to appear in one cell (preferably separated by a comma). For example, the defined name list contains the following data:
Choice 1 Choice 2 Choice 3 Choice 4
I would like to be able to populate one cell with the following via the drop down list: Choice2, Choice 4
VBA code which can distinguish whether a cell has been edited by:
selecting an option from a data validation list (DVL) or manually editing the text in a cell?
The ""Show error alert after invalid data is entered"" option is unticked to allow both selection from the DVL as well as free format text entries.
The problem arises when using the code below which makes an edited cell within a range display the new value as well as the prior value(s). This works well when using the DVL but not when editing the cell.
Is there any code which can distinguish between the 2?
I'm not sure if its possible to do what I want. I'm trying to do it without VBA as my users might have a high macro security setting.
I have a validation dropdown in A2 where they pick 'yes' or 'no' for FLSA. I would like B2 to be a dropdown (validation, listbox, whatever) that would change based upon A2. If A2 is 'yes' I want to use the data in A5:B7 and if its 'no' use A10:B12. Ultimately I want B2 to equal one of the numbers from A5 - A7 or A10 - A12. However when they click on the drop down they see the combo of column A and columb B as column A has no meaning for the user.
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create Name: Fruit
Refers to: banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
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
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
I have a List of Different Fruits in Cells A1 to A5
Apple Banana Orange Strawberry Cherry
And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,
But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....
I Used the formula
=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)
But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)
I am attempting to use a named range as my Validation data source but have two questions:
1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range
2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.
I would like to exclude blanks from the source of a validation list, I have found a really neat solution for vertical list in this topic, I have modified it, but I have missed something, the formula is not working.
i mtrying to get a validation list change depending on what is chosen in another list. I have attached an example, Yellow box is my validation and weather List 1 or List 2 in chosen I want the red box to be a choose of the list attached to those options. I've tried to put an If in there but I’m al a loss.
