Data Validation : Changing cell color when using a Data Vali

    I'm using a data validation list and i'd like to have the cell obtain all the charecteristics (not just the value) of the selection from the list.



    i.e. If the list is composed of the values one, two and three. If a user selects "one" the word "one" appears in the cell and the colour turns to red. Pick two and "two" appears in green cell. ...


    Can this be done?


    How would I go about doing this?


    Thanks in advance for your help

    Can you use FormatConditions?


    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""1"""
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=""1"""
    Selection.FormatConditions(2).Interior.ColorIndex = 41


    here if the cellvalue is 1 the fontcolor becomes red, and if the cellvalue <> 1 the interiorcolor becomes blue.


    Woody93

    If there are too many values for Woody93's suggestion of data validation to work, you could do it with a Calculate Event macro for the sheet (a change event won't work because entries from a data validation list don't trigger it, alas).
    Steps:
    1) If the cell where the validation entry takes place is A2, in some out of the way cell put the formula =A2 so a calculation will occur when a change is made.
    2) In the Calculate macro, match the value in A2 to your validation list to find which entry it is. Then eiher format cell A2 as desired or copy the format from the appropriate cell in the validation list and paste to A2.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login