help in changing the code that runs on one cell to the whole column

**hiteshasrani43** · 06-06-2012, 02:34 PM

Hi All,

I am very to this forum, need a lil help with the following code.

Please Login or Register  to view this content.

This code helps me with the following,

Once i select "closed" from the drop down in the D8 then a macro runs to check if certain cells are blank on the left hand side

The code work very well,

The problem is this work only for D8, I want it for D8:D65000.

Please help :D

**Pichingualas** · 06-06-2012, 02:48 PM

Check if this does what you need:

Please Login or Register  to view this content.

**Pichingualas** · 06-06-2012, 03:25 PM

Sorry, change it like this:

Please Login or Register  to view this content.

I hadn't realized that the row where you select the value from the dropdown should be the same where you execute the Check macro, but with this change it should work like you wanted. I hope this helps.

**hiteshasrani43** · 06-06-2012, 04:00 PM

Hi But that does not seem to run

... I tried both the methods...

Could you please explain the terminologies

Thanks in Advance

****Hitesh****

**hiteshasrani43** · 06-06-2012, 04:04 PM

Hi Pichingualas,

Well the following worked

[CODE]

Dim RowCheck As Long
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D8:D65000")) Is Nothing Then Exit Sub
If Target = "closed" Then
RowCheck = Target.Row
Check
End If
End Sub

Sub Check()
Dim rngCheck As Range: Set rngCheck = ActiveSheet.Range("A" & RowCheck & ":U" & RowCheck & ",X" & RowCheck & ":AB" & RowCheck)
Dim iCell As Range
For Each iCell In rngCheck
If IsEmpty(iCell) Then
iCell.Select
MsgBox ("The selected Cell is empty, Kindly fill it with the appropriate data")
Exit Sub
End If
Next iCell
End Sub
[\CODE]

Thanks a lot for the help

still need y we write NOT INTERSECT

Thanks

**Pichingualas** · 06-06-2012, 04:21 PM

Actually if you want to use it like that you should delte the Not.
The line If Not Intersect(Target, Range("D8:D65000")) Is Nothing Then Exit Sub says that if target is in "D8:D65000" then do nothing.

**hiteshasrani43** · 06-06-2012, 06:14 PM

But I tried and the files seems to work perfectly :|

**hiteshasrani43** · 06-07-2012, 10:29 AM

Hey Hi,

I m sorry your idea worked and mine failed...

The error was :
If Not Intersect(Target, Range("D8:D65000")) Is Nothing Then

This was for the column D, but i was trying for column AC :p

Well it worked...

now only one concern, I want the cell to be empty wen the Check() Sub is called..

Please help