Excel VBA - Run macro based upon Active Cell

Question

I'm creating an Excel macro that will filter a source worksheet based upon several criteria. Basically, the macro says if you're in the Ford sheet, go to the source sheet and filter on ford. That part is easy. What I'm struggling with is telling it to filter the source sheet based upon the active cell. So...

If ActiveCell is A1, go to source and perform a filter of X number of criteria, else if ActiveCell is B1, go to source and perform this other filter, and so on and so forth.

Here's what I have so far:

If ActiveCell = Range("C36") Then
    Sheets("Source-Cars").Select
        'Unfilter data
        Application.Goto (Sheets("Source-Cars").Range("A1"))
        If ActiveSheet.FilterMode Then 
           ActiveSheet.ShowAllData
        end if
end if

The problem here is that ActiveCell = Range("C36") is looking at the value of that cell, not whether or not I've selected the cell. So, if I select another cell with the same value as C36, it performs the filter instead of returning the error message box I built in.

Thoughts on how to run the logic based upon what cell is active (versus the value of said cell)? Thanks!

G

ale10ander ale10ander · Accepted Answer · 2015-06-24T17:33:03

You want to use ActiveCell.Address It will tell you a value like $A$1, which you can then use in your comparison.

Try this sample code to see how the returned value is formatted:

Sub selectRange() MsgBox ActiveCell.Address End Sub

For your case, you'd want something like

If ActiveCell.Address = "$C$36" Then

Excel VBA - Run macro based upon Active Cell

1 Answers