Run Macro if selection in specific column?

[dwest100]

If I want to restrict the Selection in the following macro to column C, how would I go about doing that??

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A2").Value = Application.Sum(Target.SpecialCells(xlCellTypeVisible)) / 1440
End Sub

Thanks!

 

[R_Stephens]

Hi there,

Not 100% sure what you are after here, but the following will only update cell A2 if your selection is in column C (or the top left hand cell if you select a range of cells)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
Range("A2").Value = Application.Sum(Target.SpecialCells(xlCellTypeVisible)) / 1440
End If
End Sub

Hope this helps, if its not exactly what your after, if you can clarify a little more then we can adjust this to meet your requirements.

Richard

 

[dwest100]

Hi Richard,
That's almost what I need. It works great except if I select a range that has a "C" cell in the top left as you say.

I need the macro to run only if the selected cells are in the C column. If any other cells are selected along with the C cells, then the macro should not run.

Can it be tweaked to accomodate that??

Many thanks for the quick response!
Don

 

[R_Stephens]

Hi there,

Sorry for the delay in responding, works been keeping me busy!!

Try the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = 0
For Each cell In Target
If cell.Column <> 3 Then x = 1
Next cell
If x = 0 Then
Range("A2").Value = Application.Sum(Target.SpecialCells(xlCellTypeVisible)) / 1440
End If
End Sub

Hope it helps

Richard

 

[dwest100]

Beautiful!

I'm learning to think like a programmer by studying the coding techniques. I'm sure that comes easy for you.

Is there a more efficient way for me to learn how to code macros??

Many thanks for your help, it works perfectly!

best regards,
Don