Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to color row doesn't work with filtered rows

0

I created a simple macro to select and color a row. However my spreadsheets are rather large with 155 columns and upwards of 500 rows. When working with such a large spreadsheet I use autofilter to isolate data. When certain columns are filtered the macro only selects the row without coloring, but works fine when the columns are unfiltered to see all data.

Sub BlueMacro()
' ' Blue Macro ' ' Keyboard Shortcut: Ctrl+e '

Rows(ActiveCell.Row).Select

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End Sub

I have been unable to find any help with this. Any help would be greatly appreciated.

UPDATE: I have attached a dummy copy of my spreadsheet. This will make it easier to duplicate the problem. I frequently filter by F38 and others.
excelmacros-vba
MS Office Version: 2007
posted by: spadrine454 (rep: 2) on Sat Apr 8, 2017 at 6:57 pm
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

This macro seems to work fine for me. I copied it into a spreadsheet, filtered some data, selected a cell in the desired row, hit Alt+F8 and ran the macro and it worked exactly as you described it should.

Are you using another macro to call this one? Maybe there is something else going on in your process.
don (rep: 1989) Apr 9, '17 at 1:04 am
Hi Don, and thanks for answering. At first I thought it had something to do with the fact that I run a different macro to hide certain columns that I don't need to see. However, even when those are hidden the macro works fine when columns are unfiltered. I'm thinking I will attach a sample spreadsheet to see if you can duplicate my problem. I'll post it tomorrow. Because of the nature of my job, I work with sensitive information so I'll have to put dummy info into it if that helps.
spadrine454 (rep: 2) Apr 9, '17 at 10:09 pm
Thank you Don! The simple fix you did with the macro did the trick! I won't further embarrass myself telling you how many months I've pondered the problem. I guess most answers are very simple after all.
spadrine454 (rep: 2) Apr 13, '17 at 6:44 pm
Add to Discussion

Answers

0
Selected Answer

I just removed the extra crap that Excel put in there when you made the macro using the Macro Recorder and now it seems to work fine.

Sub BlueMacro()

Rows(ActiveCell.Row).Select

With Selection.Interior
.Color = 15773696
End With

End Sub
posted by: don (rep: 1989) on Wed Apr 12, 2017 at 6:16 am
Discuss

Discussion

Thanks, Don!  I'll test this out tomorrow and see if it works for filtered rows and let you know.
spadrine454 (rep: 2) Apr 12, '17 at 7:33 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login