Solved Change cell value by entering date in another cell

October 4, 2013 at 09:43:01
Specs: Windows 7
I have two columns. Each box in my first row contains a drop down box with 4 options. (payable, Receivable, none and complete.) I have rules in place already that change the fill color of the cell based on what selection is made. I would like to make the first row automatically change to "Complete" and be colored red whenever a date is added in the second column. Can someone help me with the conditional formatting rule I need in place?

See More: Change cell value by entering date in another cell

Report •

#1
October 4, 2013 at 11:47:08
Color me confused...

re: "I would like to make the first row automatically change to "Complete" and be colored red whenever a date is added in the second column."

You want the first row to read Complete and be Red when a date is added to the second column?

I'm missing the connection between the first row and the second column.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
October 7, 2013 at 05:02:07
in the first column, each cell contains a dropdown box. Our administrator selects weather this item is payable, Receivable, none, or complete. The sheet is then opened by our payroll group and they deal with everything that says payable or receivable. When they finish the paperwork for that item they put a date in the second column indicating when the file has been closed. I would like the first column to automatically turn to "complete" when a date is entered in the second column

Report •

#3
October 7, 2013 at 06:55:08
✔ Best Answer
In your OP you said: "Each box in my first row contains a drop down box with 4 options"

Now you say: "in the first column, each cell contains a dropdown box"

I'm sure you can now see why I was confused.

In order to force a cell with a Drop Down box to show a specific value based on the entry in another cell, you have to use a Macro. Without the Drop Down, you could use the IF function, but you can't use a Drop Down and a formula in the same cell.

Of course, this means that all users of the spreadsheet have to have Macros enabled on their system. You cannot "force" another user's Excel application to run a macro. It has to be enabled by the user through Excel's macro security settings on their own machine.

One other item: You used the words "first column" and "second column" but you didn't specifically say which columns those were. For the code below, I assumed you meant Columns A and B. If that is not correct, change the numbers in the lines after the comments with the arrows to represent the column where the date will be entered and in the line where Complete should be shown. You must use the number of the Column, not the letter. A = 1, B= 2, AA = 27, etc.

If you change the Column numbers to match your columns, you should also change the Column letters in the green comments to avoid confusion.

Right-click the sheet tab for the sheet with the DropDowns and chose View Code. Paste this code into the pane that opens. Go back to your sheet and enter a Date in the appropriate column. I am assuming that your Conditional Formatting is already set to turn the cell Red when Complete is entered.

Private Sub Worksheet_Change(ByVal Target As Range)
'Ensure only one cell has been changed
  If Target.Cells.Count = 1 Then
'Determine if change was in Column B (2)
'--> Set number in the next line to Column with Date <--
    If Target.Column = 2 Then
'If a Date was entered, Set Column A (1) to read Complete
      If IsDate(Target) Then
'--> Set number in the next line to Column with Drop Down <--
        Cells(Target.Row, 1) = "Complete"
      End If
    End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 7, 2013 at 08:14:57
Thank you so much for your help. It worked perfectly!

Report •

Ask Question