Can excel Filter formula filter by font colour?

[tonywatsonhelp]

Hi Everyone,
does anyone know if you can filter by font colour using the new filter formula ?
if so please give example of filtering by RGB 255,0,0

if not any ideas of how i can do this in vba?

thanks
Tony

 

[Skyybot]

This will go through a Range (I used Sheet1, Columns 1 (or A)) and tell you which cells have red font as Debug.Print in Immediate Window.

Sub clrFilter()
Dim myRng As Range, cell As Range
Set myRng = Sheet1.Range(Cells(1, 1), Cells(Sheet1.UsedRange.Rows.Count, 1))
For Each cell In myRng
    If cell.Font.Color = 255 Then
        Debug.Print "Cell " & cell.Address & " has red font."
    End If
Next cell
End Sub

 

[Skyybot]

This will go through a Range (I used Sheet1, Columns 1 (or A)) and tell you which cells have red font as Debug.Print in Immediate Window.

Sub clrFilter()
Dim myRng As Range, cell As Range
Set myRng = Sheet1.Range(Cells(1, 1), Cells(Sheet1.UsedRange.Rows.Count, 1))
For Each cell In myRng
    If cell.Font.Color = 255 Then
        Debug.Print "Cell " & cell.Address & " has red font."
    End If
Next cell
End Sub

Code is placed in Sheet1.

 

[tonywatsonhelp]

Thanks Skyybot,
I can probably use this and delete what i dont want
Thanks
Tony

 

[Fluff]

The Filter function cannot see colour, so you need VBA.
To get the code you can turn on the macro recorder & apply an autofilter to filter the colour & you have your code.

 

[frabulator]

I have thrown together a quick code that checks the current cell text color, changes it, and then converts back.

You could possibly use something like this to loop through existing cells and do things if the RGB values match.

(also, Excel uses a default 'long' formation instead of RGB)

Sub TestColor()
    
    Dim OriginalColor As Long
    OriginalColor = GetFontColor(Selection)
    
  
    Dim RGB1 As String
    RGB1 = RGB1 & ConvertLongToRGB(Selection.Font.Color, "R")
    RGB1 = RGB1 & ", " & ConvertLongToRGB(Selection.Font.Color, "G")
    RGB1 = RGB1 & ", " & ConvertLongToRGB(Selection.Font.Color, "B")
    MsgBox (RGB1 & " Is the original color of selected cell - Long value of: " & OriginalColor)
    

    Dim NewFontColor As Long
    NewFontColor = ConvertRGBToLong(255, 0, 0)
    SetFontColor Selection, NewFontColor
    
    Dim RGB As String
    RGB = RGB & ConvertLongToRGB(Selection.Font.Color, "R")
    RGB = RGB & ", " & ConvertLongToRGB(Selection.Font.Color, "G")
    RGB = RGB & ", " & ConvertLongToRGB(Selection.Font.Color, "B")
    
    MsgBox (RGB & " Is the new color of selected cell - Long value of: " & NewFontColor)
    
    MsgBox ("Now we will revert back to the way it was.")
    SetFontColor Selection, OriginalColor
    
    

    
End Sub

Function SetFontColor(ByVal Target As Range, Col As Long) As Long
    
    Target.Font.Color = Col
    
End Function


Function GetFontColor(ByVal Target As Range) As Long
    GetFontColor = Target.Font.Color
End Function




Public Function ConvertRGBToLong(iRed As Integer, iGreen As Integer, iBlue As Integer) As Long
    ConvertRGBToLong = RGB(iRed, iGreen, iBlue)
End Function

Public Function ConvertLongToRGB(lColor As Long, sRGB As String) As Integer
    Select Case UCase(sRGB)
        Case "R"
            ConvertLongToRGB = lColor Mod 256
        Case "G"
            ConvertLongToRGB = lColor \ 2 ^ 8 Mod 256
        Case "B"
            ConvertLongToRGB = lColor \ 2 ^ 16 Mod 256
    End Select
End Function

This is the page I used for reference when converting colors back and forth.