Filter by color of current cell

jstiene

Board Regular
Joined
Aug 5, 2005
Messages
223
I have a macro and function that doesn't seem to work despite the variable being identical to hardcoded RGB colors.
It sends the address of the current cell to a get RGB function and builds a string identical to "RGB(255, 255, 0)"

but why does the commented out line work but not the one with a variable? Obviously I am using variables to pass current column, a string address. Does Crit one need to be a variant?

ActiveSheet.Range(Add2).AutoFilter Field:=CurrentCol, Criteria1:=Crit1, Operator:=xlFilterCellColor
'ActiveSheet.Range(Add2).AutoFilter Field:=CurrentCol, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor



Sub FilterToCurrentColor()
Dim Add1 As String, Add2 As String, Crit1, CurrentCol As Integer
Add1 = ActiveCell.Address(False, False)
''''''''''''''''''''''''''''
Crit1 = getRGB2(Add1)
''''''''''''''''''''''''''''''
CurrentCol = ActiveCell.Column
ActiveSheet.AutoFilterMode = False
Add2 = "$A$1:$BH$2000"
ActiveSheet.Range(Add2).AutoFilter Field:=CurrentCol, Criteria1:=Crit1, Operator:=xlFilterCellColor
'ActiveSheet.Range(Add2).AutoFilter Field:=CurrentCol, Criteria1:=RGB(255, 255, 0), Operator:=xlFilterCellColor
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function getRGB2(rcell As String) As String
Dim C As Long
Dim R As Long
Dim G As Long
Dim B As Long
C = Range(rcell).Interior.Color
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256
getRGB2 = "RGB(" & R & ", " & G & ", " & B & ")"
' Debug.Print getRGB2
End Function
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I suspect that it's because your passing a string & the filter needs numbers.
Try
Code:
Crit1 = ActiveCell.Interior.Color
 
Upvote 0
Why does it have to be RGB?

My Mistake. I guess when I was recording it to see what filterby color generated it used RGB. So I found a function to extract it, but it is much simpler to just get activecell.interior.color then populate a variable and use that.
 
Upvote 0
The revised is much simpler to filter the current cell's column to the color your cursor is in. Thanks. I don't knwo why I thought it required RGB. I don't think I've ever even seen RBG in Excel code.

Sub FilterToCurrentColor()
Dim Add1 As String, Add2 As String, Crit1, CurrentCol As Integer
Add1 = ActiveCell.Address(False, False)
Crit1 = ActiveCell.Interior.Color
CurrentCol = ActiveCell.Column
ActiveSheet.AutoFilterMode = False
Add2 = "$A$1:$BH$2000"
ActiveSheet.Range(Add2).AutoFilter Field:=CurrentCol, Criteria1:=Crit1, Operator:=xlFilterCellColor
End Sub
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
OK, I think I know why Excel macros generate RBG instead of cell color or interior color. Cell color doesn't work on conditional formatting colors. RGB does.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top