Formula and Manual user input in same cells of the Column

[chintanbtech]

Hi,

Please find below the Sample spreadsheet.

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]ACD[/TD]
[TD]AVAL[/TD]
[TD]AVALCD[/TD]
[TD]AVCDREF[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Cat[/TD]
[TD]C for Cat[/TD]
[TD]Animal[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Dog[/TD]
[TD]D for Dog[/TD]
[TD]Animal[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Elephant[/TD]
[TD]E for Elephant[/TD]
[TD]Animal[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]Fox[/TD]
[TD]F for Fox[/TD]
[TD]Animal[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]Grape[/TD]
[TD][/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD].......[/TD]
[TD].......[/TD]
[TD].......[/TD]
[TD].......[/TD]
[/TR]
</tbody>[/TABLE]




Is it possible to create a macro which fulfill below conditions?
If in Column D (AVCDREF) the value is Animal then in Column C (AVALCD) formula should appear as =IF(D2="Animal",A2&" for "&B2,"").
If in Column D (AVCDREF) the value is Fruit then in Column C (AVALCD) Formula should not be appeared and user should allow to enter any value in the corresponding cell of Column C. In case in Column D (AVCDREF) the value is updated to Animal then again formula should appear.


Thank you for your help!

Regards,
CP

 

[footoo]

There's no need to clear the formula in column C if column D does not contain "Animal" :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [D:D])
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cel In rng
    If LCase(cel) = "animal" Then _
    cel(1, 0).FormulaR1C1 = "=IF(RC[1]=""Animal"",RC[-2] & "" for "" & RC[-1],"""")"
Next
Application.EnableEvents = True
End Sub

If you wish, you could prevent column C being changed if column D contains "Animal":

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [C:C])
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cel In rng
    If LCase(cel(1, 2)) = "animal" Then cel(1, 2).Select
Next
Application.EnableEvents = True
End Sub

Note : This second procedure does not prevent changes by dragging into column C, or by "pasting through" column C.

 

[chintanbtech]

Hi Footoo,

Thank you for the prompt response.

The Code is working perfectly fine when I enter the values in Column D (AVCDREF) however in my sheet the values of Column D are populating using formula and the code is not working if Column D has formula .

Regards,
CP

 

[footoo]

Try:

Private Sub Worksheet_Calculate()
Dim rng As Range
Set rng = Range([C2], Cells(Rows.Count, "D").End(xlUp)(1, 0))
Application.EnableEvents = False
rng.Formula = "=IF(D2=""Animal"",A2 & "" for "" & B2,"""")"
Application.EnableEvents = True
End Sub

 

[footoo]

Or perhaps :

Private Sub Worksheet_Calculate()
Dim rng As Range, cel as Range
Set rng = Range([D2], Cells(Rows.Count, "D").End(xlUp))
Application.EnableEvents = False
For Each cel In rng
    If LCase(cel) = "animal" Then _
    cel(1, 0).FormulaR1C1 = "=IF(RC[1]=""Animal"",RC[-2] & "" for "" & RC[-1],"""")"
Next
Application.EnableEvents = True
End Sub

 

[chintanbtech]

Thank you Footoo for your help. The code is working perfectly fine.