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
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