Range not changing in macro

[generalgort]

Hi Guys,

I have written a macro for a spreadsheet so that when one of the header 'buttons' is clicked, the cells containing that colour are filtered.

The problem is whichever 'button' is pressed, the first row in the column is always filtered in regardless of it's colour, and changing the range doesn't seem to work.

Here's the code:

Private Sub Button2100_Click()

ActiveSheet.Range("$G$7:$G$28").AutoFilter field:=1, Criteria1:=RGB(255, _
255, 0), Operator:=xlFilterCellColor

End Sub

Private Sub ButtonLO_Click()

ActiveSheet.Range("$G$7:$G$28").AutoFilter field:=1, Criteria1:=RGB(83, _
126, 213), Operator:=xlFilterCellColor

End Sub

Private Sub ButtonEE_Click()

ActiveSheet.Range("$G$7:$G$28").AutoFilter field:=1, Criteria1:=RGB(250, _
192, 144), Operator:=xlFilterCellColor

End Sub

Private Sub buttonClear_Click()

ActiveSheet.Range("$G$7:$G$28").AutoFilter field:=1

End Sub


Hope you can help


Phil.

 

[SydneyGeek]

It's because you have all the filters set to Field:=1
Change those to 2, 3 and 4 to get columns B, C and D.

Denis

 

[generalgort]

Hi SydneyGeek,

Thanks for your reply, the 'buttons' are in the header of just one column, the idea being that when one of the three header 'buttons' is clicked, that the filter shows only the cells of that matching colour.

I did try your suggestion and I couldn't get it to work in this instance,

Phil.

 

[SydneyGeek]

Hi Phil,

I guess I misunderstood. The code I gave you will filter Column A for the first colour, Column B for the second, and so on. Do you need instead to filter *all* columns in the table for whichever button you click?

Denis

 

[generalgort]

Hi Denis,

Sorry I didn't make myself clear, oh and by the way I am a real VBA newbie.

It really is just one column, but with 3 headers, each of which are coloured using 'Conditional Formatting'.

The user the enters one of 3 things, the time '21:00', 'LO' or a time between '16:00' and '20:55' and the relevant colour is then added to that cell.

What I am trying to do is make each of the three headers into buttons that will filter the cells of the relevant colour in just that column.

I hope that makes it clearer, if not how can I attach a copy of the spreadsheet to my post?

Phil.