Selecting Occupied Rows without Fill Color

[MR3]

this is what I am trying to do. I would like to highlight the entire row if and only if none of the cells within that row are Filled in with any color.

 

[Domenic]

Try the following macro, which needs to be placed in a regular module (Alt+F11 > Insert > Module > Copy/Paste > Alt+Q)...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] FoundRows [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Rw [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Cell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] CellCount [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]With[/color] ActiveSheet.UsedRange
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Rw [color=darkblue]In[/color] .Rows
            CellCount = Rw.Cells.Count
            [color=darkblue]For[/color] [color=darkblue]Each[/color] Cell [color=darkblue]In[/color] Rw.Cells
                Cnt = Cnt + 1
                [color=darkblue]If[/color] Cell.Interior.ColorIndex <> -4142 [color=darkblue]Then[/color]
                    [color=darkblue]Exit[/color] [color=darkblue]For[/color]
                [color=darkblue]Else[/color]
                    [color=darkblue]If[/color] Cnt = CellCount [color=darkblue]Then[/color]
                        [color=darkblue]If[/color] FoundRows [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                            [color=darkblue]Set[/color] FoundRows = Rw
                        [color=darkblue]Else[/color]
                            [color=darkblue]Set[/color] FoundRows = Union(FoundRows, Rw)
                        [color=darkblue]End[/color] [color=darkblue]If[/color]
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Next[/color] Cell
            Cnt = 0
        [color=darkblue]Next[/color] Rw
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundRows [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        FoundRows.Select
    [color=darkblue]Else[/color]
        MsgBox "No rows found...", vbInformation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
                    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[MR3]

Hi Domenic,

I tried the code and it just highlights all the cells. It doesn't select the rows that contain no fill colors. I can send you my xl file so you can try the code yourself to verify.

Here is the screenshot of the output:

 

[Domenic]

When I run the macro based on your sample data, A6:E7 gets highlighted. Do you have separate data on the same worksheet?

 

[MR3]

Hmm the data that i posted is the only data that exists.
There are two conditional formats set for the range of data given.
1. highlight in YELLOW blank cells
2. highlight in RED cells over 545

can you post a screen shot of what you see on your end?

 

[Domenic]

In that case, we can test using the same logic as Conditional Formatting. Try replacing...

If Cell.Interior.ColorIndex <> -4142 Then

with

If Cell.Value = "" Or Cell.Value > 545 Then

 

[MR3]

ok that worked PERFECTLY!

thank you very much.

 

[Domenic]

As per your request, here is the code with comments included...

[font=Verdana][color=green]'Force explicit declaration of variables[/color]
[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=green]'Declare the variables[/color]
    [color=darkblue]Dim[/color] FoundRows [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Rw [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Cell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] CellCount [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]With[/color] ActiveSheet.UsedRange
        [color=green]'Loop through each row within the used range[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Rw [color=darkblue]In[/color] .Rows
            [color=green]'Count the number of cells in the current row[/color]
            CellCount = Rw.Cells.Count
            [color=green]'Loop through each cell within the current row[/color]
            [color=darkblue]For[/color] [color=darkblue]Each[/color] Cell [color=darkblue]In[/color] Rw.Cells
                [color=green]'Keep a count of each iteration[/color]
                Cnt = Cnt + 1
                [color=green]'If current cell is blank or has a value greater than 545, exit For[/color]
                [color=darkblue]If[/color] Cell.Value = "" [color=darkblue]Or[/color] Cell.Value > 545 [color=darkblue]Then[/color]
                    [color=darkblue]Exit[/color] [color=darkblue]For[/color]
                [color=darkblue]Else[/color]
                    [color=green]'If current cell is the last cell in the row, assign the row to an object variable[/color]
                    [color=darkblue]If[/color] Cnt = CellCount [color=darkblue]Then[/color]
                        [color=darkblue]If[/color] FoundRows [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                            [color=darkblue]Set[/color] FoundRows = Rw
                        [color=darkblue]Else[/color]
                            [color=darkblue]Set[/color] FoundRows = Union(FoundRows, Rw)
                        [color=darkblue]End[/color] [color=darkblue]If[/color]
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Next[/color] Cell
            [color=green]'Re-set the count to zero[/color]
            Cnt = 0
        [color=green]'Go to the next row within the used range[/color]
        [color=darkblue]Next[/color] Rw
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]'If rows have been assigned to the object variable FoundRows, select the rows.[/color]
    [color=green]'Otherwise, display a message alerting the user that no rows were found[/color]
    [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundRows [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        FoundRows.Select
    [color=darkblue]Else[/color]
        MsgBox "No rows found...", vbInformation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
                    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]