Color Cell When Validation List Changes

Apr 18, 2008

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!

View 6 Replies


ADVERTISEMENT

Configure A Cell For Color Validation?

Jul 8, 2006

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.

View 3 Replies View Related

Validation List From A Single Cell

Jan 4, 2008

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.

View 9 Replies View Related

Data Validation List Based Off Cell Value

Apr 25, 2014

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.

TIMESHEET sample.xlsx

View 9 Replies View Related

Validation List Based On Another Cell (formula Only)

Jul 4, 2014

I seek to create a date validation list based on information is another cell (see images)

Based on (data validated list) Cell B4 - have a validation list drawn from another tab on the same worksheet "Sub regions"

equally the same with sub regions and citys...

View 6 Replies View Related

Change Cell Value When Validation List Is Changed?

Mar 11, 2014

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.

View 3 Replies View Related

Update A Validation List Based On Value Of Another Cell

Mar 5, 2009

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.

View 9 Replies View Related

Username Entered In Cell When Validation List Is Used?

Apr 25, 2014

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?

View 9 Replies View Related

Cell Color To List Of Values

Aug 14, 2006

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

'Create the column headers,
Range("A1").Select
ActiveCell.Formula = "Color"
ActiveCell.Offset(0, 1).Formula = "Index"
ActiveCell.Offset(0, 3).Formula = "Red"
ActiveCell.Offset(0, 4).Formula = "Green"
ActiveCell.Offset(0, 5).Formula = "Blue"

ActiveCell.Offset(1, 0).Activate

View 6 Replies View Related

Apply Conditional Formatting If The Cell Contains Validation List?

Jul 13, 2013

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.

View 3 Replies View Related

Dropdown List Validation - Associate With Same Previous Cell

Jan 8, 2012

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.

View 2 Replies View Related

Data Validation In That Large Merged Cell Using List And =Cements

Mar 25, 2009

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?

View 5 Replies View Related

Single Cell Comma Separated Values To Validation List

May 31, 2013

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.

View 2 Replies View Related

Change Cell Color Based On A List Of Dates

Oct 30, 2013

I am having an excel sheet where I enter the delivery dates. There are few freezes and restricted dates.

When I am entering the delivery date which falls under the freeze or restricted date, the colour of that cell should change.

How to achieve this either using some macro.

View 4 Replies View Related

Changing Cell Text Color When Out Of List Scope

Feb 5, 2009

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.

View 12 Replies View Related

Highlight Given Color On Left Cell Of Dropdown List?

Mar 19, 2012

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.

View 1 Replies View Related

Two Cells Data Validation List Should Be Populated In Third Cell With Unique Values?

Apr 21, 2014

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.

View 9 Replies View Related

Select Multiple Criteria From Validation List Dropdown (populate Same Cell)

Aug 8, 2012

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

Is this possible and if so, how?

View 8 Replies View Related

VBA - Distinguishing When Cell Is Edited By Data Validation List Or Free Format Text

Mar 20, 2014

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?

[Code] ....

View 5 Replies View Related

Link Validation List To Another Validation List?

Jul 1, 2013

I have a list for example with two variants "YEs, "NO"..

Is there any possibility to choose YES or no in any cell an reflect the same value in another list on another sheet.

View 8 Replies View Related

Validation List To Depend On Another Validation List

Sep 9, 2006

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.

View 9 Replies View Related

Data Validation List Automatically Show The First Item In The List Rather Than Showing Blank

Jun 20, 2008

I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.

View 10 Replies View Related

Named List: Data Validation To Restrict The User To Only Selecting Values In A List

Jun 15, 2007

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.

View 4 Replies View Related

Created Validation Drop-down List - Can I Make The Row Now Change To The Colour That Relates To The Selection From The List

Jan 13, 2009

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?

View 9 Replies View Related

How To Get Unique List From A Column To Data Validation Dropdown List

Oct 28, 2011

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?

View 5 Replies View Related

Excel 2007 :: Data Validation List Depend On Another DV List

Apr 29, 2012

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)

View 4 Replies View Related

Validation List Based On Named Range Not Restricted To List

Dec 15, 2007

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.

View 9 Replies View Related

Exclude Blanks In Validation List From Horizontal List

Apr 7, 2013

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.

View 2 Replies View Related

Validation List Change Depending On What Is Chosen In Another List

Mar 19, 2009

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.

View 3 Replies View Related

Setting Widths List Box In Data Validation List

May 12, 2004

dear....can i set width in list box data validation (width in cell i set short).thanks

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved