How to repeat the macro for all cells in a column with reference to the adjacent column cell is not empty

[raghavanhr]

Dear Friend,

Please help me to run the macro for all cells of a particular column, with reference to the adjacent cell till it is not an empty cell.

eg. column I refers to the cell value to execute the Macro in column J.

If the cell value of I =0, J value should be as "Can issue" else "Material Return Pending"

I have written the macro as below


"Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("I2").Value = 0 Then
Range("J2").Value = "Can Issue"
Else
Range("J2").Value = "Maretial Retun Pending"
End If
End Sub
"

I don't know how to attach my excel sample file.

pasted below the values of my excel

[TABLE="width: 1372"]
<tbody>[TR]
[TD]SR.NO[/TD]
[TD]DATE[/TD]
[TD]ID NO.[/TD]
[TD]PART. ID[/TD]
[TD]DESCRIPTION[/TD]
[TD]UOM[/TD]
[TD]ISSUE QTY[/TD]
[TD]RETURN QTY[/TD]
[TD]NEW ISSUE[/TD]
[TD]Material Issuance Check[/TD]
[TD]SHIFT[/TD]
[TD]PROJECT[/TD]
[TD]REMARKS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/02/2019[/TD]
[TD]801[/TD]
[TD]81000000499[/TD]
[TD]EAR PLUG[/TD]
[TD]NOS[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]Can Issue[/TD]
[TD]DAY[/TD]
[TD]JF1801[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]02/02/2019[/TD]
[TD]1353[/TD]
[TD]81000000499[/TD]
[TD]EAR PLUG[/TD]
[TD]NOS[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD]JF1801[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02/02/2019[/TD]
[TD]1684[/TD]
[TD]82100000040[/TD]
[TD]WHITE MARKER[/TD]
[TD]NOS[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD]JF1801[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]02/02/2019[/TD]
[TD]894[/TD]
[TD]81000000219[/TD]
[TD]TWECO CONNECTOR 120MM MALE[/TD]
[TD]NOS[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD]JF1801[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]02/02/2019[/TD]
[TD]894[/TD]
[TD]81000000219[/TD]
[TD]TWECO CONNECTOR 120MM FEMALE[/TD]
[TD]NOS[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD]JF1801[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]02/02/2019[/TD]
[TD]952[/TD]
[TD]76020000007[/TD]
[TD]DOTTED GLOVES[/TD]
[TD]PAIR[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD]JF1801[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]02/02/2019[/TD]
[TD]952[/TD]
[TD]81000000499[/TD]
[TD]EAR PLUG[/TD]
[TD]NOS[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD]JF1801[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]02/02/2019[/TD]
[TD]952[/TD]
[TD]81000000529[/TD]
[TD]GRINDING DISC 7''[/TD]
[TD]NOS[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]DAY[/TD]
[TD]JF1801[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jim may]

Have you considered a formula in Cell J2 (and Copied Down) like - Macro Not Needed!!

[TABLE="width: 173"]
<tbody>[TR]
[TD="class: xl63, width: 173"]=IF(I2=0,"Can  Issue","Material Retun Pending")[/TD]
[/TR]
</tbody>[/TABLE]

 

[raghavanhr]

Hi Jim,

I want that J2 column with a comment to be blinking if it is Material Return Pending, else the store guy can issue the material. This is to enhance my material issuance track.

kindly advise.
thanks,
Vijay.

 

[raghavanhr]

I don't have any formula in J2, but with reference to the value in "I" column, called the comment in J column by a macro.