Data validation with formulas

**craigproudfoot** · 01-22-2010, 02:45 PM

Hi

This one has me beat.

I have attached an example excel sheet to clarify what I am looking for and I will list the procedures here:
B11 is given a value from a drop down list
C11 is also given a value from a data validation list either "YES" or "X"
I would like to have an automatic process whereby
If B11 value is set to "CASH" - C11 changes to (fill colour green) and "YES"
I have managed to get it to change colour to green with a conditional format, but I can't change the C11 cell value to "YES"
I would like it to work so that if you then change the value of B11 to something other than "CASH", you have the option to set a new value of either "YES" or "X" from the drop down list in C11.

I would also like for C11 to be blank with no fill if B10 =0 - likewise i would like the formulas, drop down lists and formatting to work no matter when the values are changed or how many times they are changed.

I suspect this cannot be done without using VBA (which I have never used), but any pearls would be much appreciated.

This spreadsheet will be for personal use to track receipts and expenditure.

Thank you in advance

Craig

**Palmetto** · 01-22-2010, 04:33 PM

You will have to use VBA to accomplish this.

See if the attached is what you intended.

Data validation was removed from C11 and an active-x combobox used in its place.

Option Explicit

Private Sub ComboBox1_Change()

    With Me
        Range("C11") = .ComboBox1.Value
        .ComboBox1.Visible = False
    End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Range("C11")) Is Nothing Then
        Select Case Range("B11").Value
            Case Is = "CASH"
                Range("C11").Value = "Yes"
                Range("c11").Interior.ColorIndex = 5
            Case Else
                Range("C11").Interior.ColorIndex = xlNone
                Me.ComboBox1.Visible = True
        End Select
    End If

End Sub

**craigproudfoot** · 01-30-2010, 11:18 PM

Thanks for this Palmetto
I didn't mean to double post - i didn't think this post worked. After i posted it i couldn't find it - even through search, so i figured it hadn't been properly submitted.
I then re-wrote the question and re-posted.

Thanks for taking the time to look at this for me.
I will give your VBA a go and let you know how it goes.

Cheers

Craig