write a macro to fill text only if a certain value exits in another cell

[Nirmall]

Hi,

i am able to put in a specific text in a cell using a macro, but now i want to validate that macro such that it can enter data only if a particular data exists in another cell.

explanation.

i have the following code written

[Code/]
Sub inputaquablue()
Dim cell As Range
For Each cell In Selection
If cell.Column = 2 And cell.Row >= 14 And cell.Row <= 78 Then
cell.Value = "Aqua Blue"
End If
Next cell

End Sub
[Code]

By using the above i am able to enter "Aqua Blue" in column B, rows 14 to 78, however now i want to allow entry of "Aqua Blue" only if the corresponding cell has the Text or Value "Fish" in it.

Going by the above example i want to enter "Aqua Blue" in cells B20:B25, but it should input "Aqua Blue" only if cells A20:A25 has "Fish" written in them.

If however if "fish is written in only cell A21 then "Aqua Blue" should come in only cell B21.

Regards

[DBY]

Hello Nirmall
Try something like:

Please Login or Register  to view this content.

This is not case sensitive to the word 'Fish', it will accept all cases.

Hope this helps.
DBY

[Nirmall]

It says wrong number of arguments or invalid property assignment

Is it because my sheet is named as Data???

If that is the reason then what changes do i make in my code??

[bulina2k]

Hello there!
What line is highlighted by the error ?

[Nirmall]

For Each c In Data.Range("A20:A25")

I had changed the name from Sheet 1 to Data (Data is the name of my sheet)

[bulina2k]

Try this way then:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Nirmall]

Hi all, thanks for your replies, but am still not able to get the desired result, am putting my file as an attachment.

Please let me know where the problem is??

Regards

Nirmall

[DBY]

Hello
The following works for me on your sample file:

Please Login or Register  to view this content.

DBY

[JOHN H. DAVIS]

My suggestion works on your sample.

Please Login or Register  to view this content.

Add Option Compare Text above the Sub - Make a Selection.

[Nirmall]

Hi All thanks Post 9 worked well.

Thanks DBY,John Davis & bulina2k

[DBY]

Glad we could help. Thanks for the Rep mark.

DBY

[Nirmall]

Hi,sorry all,DBY if you can please help again,

the code which you provided in this excel puts Aqua blue, whenever i run the macro and when there is Fish in the corresponding cells in column A.

But there are times where i would want to assign a different color to "Fish". So instead of "Aqua Blue i would want to select White for a few cases.

Kindly give a solution where the macro is run for cells which are selected by me.
Meaning there might be Cells A20:A30 which might be having "Fish", but i would want to select "Aqua Blue" for cells B20:B25, and "White" for Cells B26:B30.
Regards