microsoft excelmicrosoft-excel-2010worksheet-function
In MS Excel 2010 Professional is there a way to automatically copy the background colour of the custom dropdown values from somewhere else in a spreadsheet?
I have some Data Validation on a row of cells that comes from two custom cells which have a background:
A53 and A54 look like this:
When selecting from the dropdown in my SS how can I configure it so that it turns green or red and not just populates with the text?
As Doktoro Reichard states, you want to use Conditional Formatting to do this. In this specific case you want to have three rules:
Apologies, my Excel is Japanese. It's multilingual day.
To do this, we need 3 formulas that will return TRUE or FALSE for each of these conditions. I will assume your data looks as follows:
The following formula will return whether or not the cell is blank. I have selected
=ISBLANK(B2)
Note that I have selected cells B2:D5 with relative references. This will apply the same formula changing the cell reference for every cell in the selected range. Set the background color to white (or whatever your preference is) when this condition is true.
The following formula will return whether or not there is a perfect match in the list on sheet 1:
=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))
The following formula will return whether or not there is no perfect match in the list on sheet 1:
=ISERROR(MATCH(B2,Sheet1!$A:$A,0))
The rule on top will be executed first. So since all blank cells will be non-matches, you need to put the blank rule first. The order of #2 and #3 doesn't matter (they will never overlap).
You've created a User-Defined Function (UDF). Excel only runs the code of a UDF when the cell(s) that serve as input to the function change.
For example, suppose I have this UDF:
Public Function MyFunction(Target As Range)
MsgBox "The target cell's address is " & Target.Address
End Function
And in cell A1 on my worksheet I reference the cell with the formula:
=MyFunction(A2)
During normal use of my workbook, the code of my UDF will only be called if there's a change to the contents of cell A2 or another cell referenced by a formula in A2.
You can work around this behavior in several ways:
Include a volatile function in one of the cells referenced by your UDF. Do this by modifying the definition of your VBA function to accept an additional parameter:
Public Function MyFunction(Target As Range, Optional VolatileParameter As Variant)
Then edit the cell referencing your UDF to pass the result of a volatile function such as Now() to the UDF:
=MyFunction(A2,Now())
The net result will be that Excel will deem the cell containing the reference to your UDF as needing to be recaculated every time the worksheet is changed because it references a volatile function.
Edit the cell containing the UDF. Simply entering the cell then pressing Enter without making changes should be sufficient to trigger an update.
Create macro button that runs the following line of code:
Application.CalculateFull
Place the following code in your Workbook's Open event:
ActiveWorkbook.ForceFullCalculation = True
This causes Excel to always recalculate all formulas, regardless of whether it believes recalculation is required. This setting remains in effect until Excel is restarted.
When Excel performs an automatic recalculation, it does not recalculate every single formula in the workbook. Instead it only updates those cells which contain formulas that refer to the most recently modified cell. This method avoids the much lengthier process of needlessly performing many calculations across the entire workbook only to come up with the same result for the vast majority of them.
With a UDF, the only references in the workbook of which Excel's calculation engine is aware are those identified in the function's inputs. Excel cannot examine the VBA code within the UDF and identify other cells that may influence the function's output. So while the function's author may construct the function to return different results based on any number of changes made in the workbook, the only cells Excel knows will change the function's result are the declared inputs. Therefore, changes to these cells are the only ones Excel pays attention to when deciding whether the UDF needs to be recalculated.
Best Answer
Data validation will not change formatting of the cells. You will need to use
Conditional Formattingto do what you desire.Once you have your lists in place, apply conditional formatting rules to those cells which use a formula that checks for the words
RESTRICTandCASCADE.Use formula to determine which cells to format=D1="CASCADE"(D1 will be the first cell in the group you selected)You will do the same for the
RESTRICTtext with red formatting. When you are done you will have two rules.You will notice the
Applies towindow will show the cells the rules are applied to (in this example I used the entire D column;$D:$D). You do not need to create a formula for each row, the rule does it for you automatically.Now when I make a selection from a list in column D, it automatically formats is according to the rules we created.