Conditional VBA - Manual Input vs. Formula

[chet645]

Hi All, I am trying to write some code that will allow me to input a value in a cell (and change the font color and cell color) if a drop down menu is toggled to say "Manual." I am not an extensive user of VBA so I am essentially trying to copy code I've found on the internet or other models I've used in the past. Needless to say, I can't seem to get it to work. Right now, when I toggle my dropdown box to Manual, nothing happens. Was wondering if anyone might have a suggestion as to why my code isn't working. Unfortunately, because I don't use VBA regularly, I am not quite sure what additional information you might need, other than posting the code I'm using, see below. If anyone can help, I would be very grateful. Kindest Regards, Chet

-------------------------------------------------------------------------------------------------

Private Sub Cap_Rate_Change(ByVal Target As Range)


If Intersect(Target, Range("AREA_CAP_RATE_SCHED_SELECT")) Is Nothing Then GoTo EndJump:
If Application.IsError(Application.Match(Target, Worksheets("Lists").Range("LIST_CAP_RATE_SOURCE"), 0)) Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

If Target.Value = "Manual" Then

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 0.04

With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = 0.04
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With

ActiveCell.Offset(0, -1).Range("A1").Select

Else

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(INDEX_MONTH,DATE_REVERSION,INDEX_EXIT_CAP)"
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With


ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(INDEX_MONTH,DATE_REVERSION,INDEX_EXIT_CAP)"
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With

ActiveCell.Offset(0, -2).Range("A1").Select

End If

Application.ScreenUpdating = True
Application.EnableEvents = True

EndJump:
Worksheets("Assumptions").Calculate
Exit Sub

Application.EnableEvents = True

End Sub

 

[My Aswer Is This]

What do you want to happen when:
I toggle my dropdown box to Manual
And what cell is the drop down box in?

 

[FDibbins]

Seems an awful run-around to just apply CF to a cell? Why not just use the built-in CF function for this?

 

[chet645]

What do you want to happen when:
I toggle my dropdown box to Manual
And what cell is the drop down box in?

The dropdown box is located in cell v69. When the dropdown box is toggled to "manual," I would like the vba code to clear out the formula in the cell to the right (w69) and replace it with a simple numerical value (e.g. .04) with formatting of the cell to be grey with font color blue. In my model, this format tells the user that the cell is now an input cell, to which the user can input any value he/she wishes.

When the dropdown is toggled back to "automatic," the code will then replaced the input value with the following formula: =SUMIF(INDEX_MONTH,DATE_REVERSION,INDEX_EXIT_CAP). Additionally, the cell formatting will return to no fill with font color black. This indicates to the user that this cell is no longer an input cell, but rather is driven by a formula and should not be touched.

I have seen this done before in other models, only slightly different. Again, I would be immensely appreciative if you have any ideas.

Thanks,

Chet

 

[chet645]

I agree, if I was just trying to change formatting, I would simply use CF. The hard part is making the cell toggle back and forth between and input cell and a formula cell (see my reply to My Answer is This) above.