How would i select all cells with the same fill colour?

[sl1990]

Hi how would i select all cells with the same fill colour? im using excel 2004 on a mac (it has VBA). is there a macro that could do this ?

cheers

 

[dfenton21]

Hi There,

This should work for you. Change the range and color as required:

Sub Macro2()

    Dim myString as string
    Dim mySelection as string

    For Each cell In Range("A1:A17")
    
        If cell.Interior.Color = vbRed Then
            myString = myString & cell.Address & ","
        End If
    
    Next cell
    
    mySelection = Left(myString, Len(myString) - 1)
    
    Range(mySelection).Select

End Sub

There may be a more elegant way of doing this than using string concatenation for the range, but it works fine.

I tested this on a PC with Excel 2003 not a Mac but it should be OK

 

[sl1990]

thanks for that dfenton21 but im getting a slight problem with it, i get a runtime error 5, invalid procedure call or argument. for this part of the macro

"mySelection = Left(myString, Len(myString) - 1)"

any ideas ?

cheers

 

[Steve=True]

I dont know if you can do this in 2004, but there is a find format feature in Find/Replace where you can select a cell format and then find all. Then highlight all inthe box that is displayed at the bottom of find/replace.

 

[dfenton21]

It is working for me perfectly, so it may be an issue with Excel 2004 on a Mac. Sorry I don't have any experience with that, so I'm unsure.

Take a look at the left and len functions so see if the syntax is correct for your version. They are very simple string functions, so I can't foresee any problems.