Formula and Manual user input in same cells of the Column

[chintanbtech]

Hi,

Please find below the Sample spreadsheet.

ACD	AVAL	AVALCD	AVCDREF
A	Apple		Fruit
B	Banana		Fruit
C	Cat	C for Cat	Animal
D	Dog	D for Dog	Animal
E	Elephant	E for Elephant	Animal
F	Fox	F for Fox	Animal
G	Grape		Fruit
.......	.......	.......	.......

<tbody>
</tbody>

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.