PDA

View Full Version : Select or Filte Row Based on Single Cell Colour?



DekHog
01-24-2014, 06:05 AM
I think I need some code for this one if anyone can assist?

I used some VBA to highlight any cell changed in the sheet with ColorIndex = 36. I now have various cells in various columns/rows coloured and need to select/filter any row with this colour in any cell in the row. I can't use filter by colour as the first and subsequent column filters could possibly exclude coloured cells in other columns..... any help appreciated.

TIA

Bob Phillips
01-24-2014, 09:43 AM
Add a simple UDF that tests a range in each row, and filter on that


Function ColouredCells(byref rng As Range) As Boolean
Dim cell As Range
For each cell In rng
If cell.Interior.Colorindex = 36 Then
ColouredCells = TRUE
Exit Next
End If
Next cell


and at the end of the row use

=ColouredCells(A2:M2)

adjust as required

DekHog
01-24-2014, 12:55 PM
Thanks a million, I'll give that a try when I get back to work on Monday.....

DekHog
01-28-2014, 01:29 AM
Hmm, the 'Exit Next' highlights as a syntax error and I don't have a scooby doo how to fix it....... :dunno

Update: For as useless as I am at this, I think I might have fixed that part..... removed the 'Exit Next' and added 'End Function' statement...... can't get it return any results though when I go to the last column and enter =ColouredCells(A2:AC2) and copy down the 651,000 rows.... I just get #NAME? down every row..... but maybe I've screwed it up myself with my editing? :yes

lecxe
01-28-2014, 04:40 AM
Hmm, the 'Exit Next' highlights as a syntax error and I don't have a scooby doo how to fix it....... :dunno


Instead of


Exit Next

try:


Exit For




...... can't get it return any results though when I go to the last column and enter =ColouredCells(A2:AC2) and copy down the 651,000 rows.... I just get #NAME? down every row..... but maybe I've screwed it up myself with my editing? :yes


This could be due to another problem. Did you paste the code in a standard module (if it's the first one you insert it's called Module1)?

DekHog
01-28-2014, 06:35 AM
Changing the 'Exit' line fixed it..... thanks for that. Perfect.

lecxe
01-28-2014, 07:25 AM
I'm glad it's working OK now. It was just a typo.