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
ADVERTISEMENT
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
View Related
Jun 25, 2009
Is it possible to modify the windows power settings in excel vba. I have this example, but it doesn't do anything.
Sub testPOWERCFG()
CreateObject("WScript.Shell").Run "POWERCFG /CREATE Custom1", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /CHANGE Custom1 /monitor-timeout-ac 15", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /CHANGE Custom1 /monitor-timeout-dc 10", 0, False
CreateObject("WScript.Shell").Run "POWERCFG /SETACTIVE Custom1", 0, False
End Sub
I need some code to create and activate a scheme whereby everything is on and also some code to revert back to the default settings on the machine.
View 3 Replies
View Related
Aug 9, 2013
Summary of performance of various products against target is as follows,
Product vs Target
Color Code
Result
CH4OH
Green
1.0
[Code] ........
I need the final result automated as follows,
If 2 green of the 4 products, then final result Gree
If 2 Amber of the 4 products, then final result amber
If 2 Red of the 4 products, final result Red
Is there a way to automate this?
View 8 Replies
View Related
Aug 8, 2012
I am trying to use VBA in Excel 2007 to change bar chart series colors. I have found a few posts that link it to a cell background, but I'm struggling to find one that does the font color.
It would be great if I could change the bar chart series to match the color of the text in the A column, so that if I highlighted the value in A1 and changed the text color to orange for whatever reason, the chart updates the value of 1.2 to an orange bar (see below).
T. A1 B1
Sample 1 1.2
Sample 2 2.1
Sample 3 1.7
Sample 4 5.6
View 3 Replies
View Related
Aug 31, 2012
I have a range of cells that change colors with conditional formats based on the cell value from high to low. I would like to link the cell color to an object such as a circle or rectangle. When the cell value changes along with the conditional format, the color of the object will also change.
View 3 Replies
View Related
Apr 11, 2008
I have got a userform with lots of controls,
One of the action's on a large group of the controls is the same but except for one number
here is an example
If TextBox107.ForeColor = 255 Then ActiveCell. Offset(0, 53).Font.ColorIndex = 3
If TextBox108.ForeColor = 255 Then ActiveCell.Offset(0, 54).Font.ColorIndex = 3
If TextBox109.ForeColor = 255 Then ActiveCell.Offset(0, 55).Font.ColorIndex = 3
This makes a cell that correlates to the textbox red if the text in the textbox is red.
Now, I loads of these textboxes that all need to run the same code with just the Offset value one digit higher than the last and I was hoping I could create a loop to avoid a huge block of code but I can't work out how to make a constant that will +1 with each loop.
Also, can I assume that a loop will start with the control with the lowest number i.e. Textbox1 and then work its way through the rest of them in order?
View 3 Replies
View Related
Nov 2, 2008
I'm trying to add a textbox at the current position (selected cell) with a set size, fill color, and border color. I found this: http://msdn.microsoft.com/en-us/libr...8(VS.80).aspx:
View 2 Replies
View Related
Nov 1, 2009
This is probably elementary, but I'm struggling and would appreciate any help as I have very little excel VBA experience to draw from.
I have assembled code which changes the cell color based on a value change in Column A. Column A will contain many different groups of repeating values. This code works well and and I have been able to figure out how to limit the number of colors to only 2. The end result is each set of similar values in column A is visually grouped by one of two alternating colors.
The number rows in the data set is variable as the data set is extracted from SAP. The number of columns is fixed.
What I want to do now is set the cell color in columns B through F the same color that was assigned to the row in column A. So if cell A3 is set to colorindex = 6, then I want to set the range of cells B3 to E3 to the same color.
Here is the code I am using to set the color of the cells in Column A:
View 7 Replies
View Related
Feb 15, 2010
I would like to be able to change the color of a cell in V4:AB31 and have the formula in AM10:AM13 automatically calculate the new result. As it is now the user has to press Ctrl ALT f9 for the formula to recalculate.
View 7 Replies
View Related
Apr 18, 2008
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 Related
Jul 9, 2009
I have font color white in blank cells in column E and I (from row 5 to row 245) so the visitors will not see the text. If any of these cells become yellow (color code is 6), the font color will become black so visitors can see the text alot far better than white. I've tried this code myself after this post but nothing happen
View 4 Replies
View Related
Oct 17, 2006
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?
View 5 Replies
View Related
Jun 22, 2007
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
View 4 Replies
View Related
Nov 18, 2007
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.
View 5 Replies
View Related
Apr 26, 2006
I've reached a problem with colors in Excel: need to convert cell color (get from .Interior.ColorIndex property) into RGB value (to set ForeColor of the button). How can I make it?
View 8 Replies
View Related
Aug 19, 2009
I'm trying to figure a to enforce dual data validation on a single cell. That is, I need to restrict the user to entering only a decimal value, only if a particular other cell (say A2) is blank. To put it another way, if A2 is blank, the user can enter a decimal value, but if A2 is not blank, the user cannot enter anything. I can use Data Validation to enforce either the decimal restriction or the ISBLANK, but I'm not sure how to make them work together.
View 2 Replies
View Related
Jun 14, 2002
I have set up a timetable for a dyslexic student and subjects need to be colour coded i have a full page and a mini timetable to filled in.
An example... I want to have excel automaticly change cell A1 to the background colour of e1 e1's cell color is defined by using vlookup and getting the colour of the cell that is to the right of it
a1 = cell colour of e1
e1 = the cell colour of
VLOOKUP(c1,a10:a20,2,TRUE)
View 9 Replies
View Related
Apr 3, 2009
I have attached a sheet that I am working on. I want cell G1 to be less than or equal to 165. That cell contains a formula. If the formula takes the number to over 165 the validation is allowing it.
View 2 Replies
View Related
May 6, 2013
I would like to add a date to one cell (say A6) and have this do two things:
#1) this would add "Closed" to a given cell such a A5.
#2) and this would add a color to a group of cells like A1 through A8.
View 4 Replies
View Related
Jan 10, 2008
I am trying to set up a data validation to ensure that when a user enters a number in column A that matches a previously entered number in column A, the user is only allowed to enter the same date for the number it matches in column B.
For example, if:
Row 3 column A = 1320
and
Row 3 column B = 16-Dec-07
and
Row 15 column A = 1320
then:
Row 15 column B must be 16-Dec-07
View 12 Replies
View Related
Jan 30, 2014
I have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
View 2 Replies
View Related
Jan 20, 2009
I'm a business/application consultant for ERP software, and generally pretty solid when it comes to excel. However, I've come across a question for a personal sheet that I can't seem to solve.
In my personal budget worksheet, I'd like to set a budget on a cell-by-cell basis. When the budget is hit, I'd like the text color (or cell color) to change.
I.E. Cell D14 has a budget of $200. When I enter $200 in the cell, the text turns from black to red. I've searched through google with no luck. I have mildly searched this forum, but haven't found my direct answer - I have come pretty close though, in other languages
View 4 Replies
View Related
May 25, 2011
I have a spread sheet and I want to conditionally format rows to be a certain color. That part I'm fine with. But I don't want them to be a set color. I have a "key" of different colored cells that I want to be the fill colors of the formatting. The ultimate goal is that for example the key looked like this
red
blue
yellow
green
then the rows I had would be formatted as red, blue, yellow, and green. But if you were to go into the key and change the first cell from red to purple, then the rows would become formatted as purple, blue, yellow, and green. Obviously I can copy formating by hand using the format painter, but I want it to update automatically.
View 4 Replies
View Related
Oct 9, 2013
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies
View Related
Jul 4, 2014
I am currently using this Formula to Search a table on sheet 2 (Tabelle2) and copy the date in the relevent cell (Cells) into the correct cell on Sheet 1 (Master Sheet).
[Code] ..........
Used in cells E10:AZ30 and E33:AZ46
This works perfectly and is the formula behind all relevent cells in Sheet 1.
I would like to keep this but to add that it also copies the background color of the cell in Sheet 2 to the cell in sheet 1.
If i can Bring this Formula into VBA and add the color changing part, It must only work on the cells listed above..
View 9 Replies
View Related
Apr 25, 2014
I would like to accomplish 2 things in my Excel 2010 spreadsheet by click a cell which already has a number and formatting in it.
1. How do I change the color of the cell, the color of the number in it, and the border around it. (Make it look like I just pressed a button by clicking it.)
2. At the same time have the text in different cell and the number in the selected cell appear in another different cell. (Text in a different cell = A , and the number in the selected cell = 23, the value in the resulting cell be "A 23") Everything I would like to happen at the same time by clicking the selected cell. I would also like this to be done several times by clicking different cells and not changing the previously selected cells.
View 1 Replies
View Related
Jun 13, 2013
I am working on a workbook with 200+ sheets and an index linking to each of the sheets. On the sheets there is a cell containing the results of calculations and I need the cell referencing the sheet in the index to change background colors depending if the calculations value is greater or less than 0. I currently have working code that also changes the tab color of each sheet depending on the value, but i am trying to add the index cell background change functionality into that. also, the index listings is subject to change. my working code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("d34").Value < 0 Then
Me.Tab.ColorIndex = 3
ElseIf Range("D34").Value > 0 Then
Me.Tab.ColorIndex = 4
Else
[Code]...
and the code i am trying to get working is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngX As Range
Set myRange = Worksheets("Index").Range("A1:A500").Find(Range("C2").Text, lookat:=xlPart)
' the C2 is the reference the title that would be on the index
If Range("d40").Value < 0 Then
[Code]....
I keep getting the runtime error '1004: application-defined error or object-defined error the thing is, i ran the cell background changing code in a normal macro before integrating it in my other worksheet code first and it worked fine. I have a little code experience, though not much in vba and I am unsure what the issue is. if there is another better way to accomplish what I'm tring to do, that would also be fine.
View 4 Replies
View Related
Jul 1, 2014
I am working on developing QR Codes using some MATLAB code and it would be really convenient if I could create an excel program which changed the background color of a cell containing a 1 to black and a cell containing a 0 to white.
View 2 Replies
View Related
Aug 7, 2014
I'm trying to create a tag with a color border. What I desire is to fill the BLANK cells around the tag, A1:D1 + D1:D19 + A1:D19 + A1:19 in a certain color based on the text value of the cell B11. There are 5 different values, such that if the B11 read Red Sox - the boarder is going to be red, if it reads Houston Astros it will be dark blue, etc..
I have a similar problem with changing the color of the cell based on the month. So regardless of the year, 2014, 2015, 2016, etc... If I use MONTH() function I can just get numbers from 1-12. I want Cell C16-C18 to be certain color depending the date entered in cell C17 such that for each quarter, months 1-3, 4-6, 7-9, 10-12 they are different color.
I have had no luck with conditional formatting (and I also believe that it is good up to 3 cases only). I am decent in logic/programming language but have little knowledge with macro notation and especially how to run them in excel 2013. I do know how to start it alt+F11 and that I need to make sure that code is written under the specific sheet where my tag is located.
View 6 Replies
View Related
