select value of active cell from a range

Question

I have a range 'Bills' (A10:B50) containing dates of my bills, some are paid on the same date so there are duplicates and blanks at the end. I have a range 'Consolidated' (D10:E30) which consolidates the dates and bill amounts into single entries. When I click on the date in 'Consolidated' it highlights the individual entries in the Bills range using conditional formatting. I use the following to get the date I am clicking on:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Range("C40").Formula = "=" & Target.Address
End Sub

However, I use C40 elsewhere, it is formatted as a date, the vba causes havoc when the active cell is not a date. How do I get my vba to work ONLY if the active cell is within the Consolidate range? Thanks

Answer 1

Not the cleanest solution, but try something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row < Range("$D$10").Row Then Exit Sub
    If Target.Column < Range("$D$10").Column Then Exit Sub
    If Target.Row > Range("$D$30").Row Then Exit Sub
    If Target.Column > Range("$D$30").Column Then Exit Sub

    ActiveSheet.Range("C40").Formula = "=" & Target.Address
End Sub

To explain, this checks the target row and columns and makes sure they are not below or above the range of your choice. If you click anywhere outside of range D10 through D30, then nothing will happen with cell C40.

I have tested this and it works. When clicking within D10 through D30, it changes the contents of C40. Hopefully it works for you.