selecting all rows with data but without a fill

[MR3]

i would like to highlight all rows that contain data (any character) but that do not contain any "fill Color" (from my conditional formatting)

 

[MR3]

I would like to automate the highlight of rows 2, 6, and 7 for example cause they do not contain a interior fill of any other color than default

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG20Jul34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, con [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray, Res [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
ReDim Ray(1 To Rng.Count, 1 To Columns.Count)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
 Dn.Select
      [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
      [COLOR="Navy"]For[/COLOR] con = 1 To 3
            Res = Evaluate(Dn.FormatConditions(con).Formula1)
          [COLOR="Navy"]If[/COLOR] Res = False [COLOR="Navy"]Then[/COLOR]
             c = c + 1
             [COLOR="Navy"]End[/COLOR] If
           [COLOR="Navy"]Next[/COLOR] con
             
             [COLOR="Navy"]If[/COLOR] c = 3 And Application.CountA(Dn.EntireRow) > 0 [COLOR="Navy"]Then[/COLOR]
             Dn.Interior.ColorIndex = 34
             [COLOR="Navy"]End[/COLOR] If
c = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MR3]

Hmm i got this Error when i tried it

 

[MickG]

If you run this in the sheet with C/F it should return the range address.
If it returns an error it would appear to be saying you have no C/F.
If that is not the case, then I don't really have an answer.

MsgBox ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Address

Mick

 

[MR3]

i ran the code and it works now but the problem is it just highlights the non conditional formatted cells to cyan, rather than selecting the actual row itself that does not contain conditional formatting. The reason for selecting the entire row without conditional formatting, would be to delete those rows without it.

 

[MickG]

I don't think this is quite what you want, but its a start, see what you think.
The code will delete rows where the C/F is false.

[COLOR=navy]Sub[/COLOR] MG21Jul45
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, con [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ray, Res [COLOR=navy]As[/COLOR] Boolean
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
ReDim Ray(1 To Rng.Count, 1 To Columns.Count)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
 Dn.Select
      [COLOR=navy]On[/COLOR] [COLOR=navy]Error[/COLOR] [COLOR=navy]Resume[/COLOR] [COLOR=navy]Next[/COLOR]
      [COLOR=navy]For[/COLOR] con = 1 To 3
            Res = Evaluate(Dn.FormatConditions(con).Formula1)
          [COLOR=navy]If[/COLOR] Res = False [COLOR=navy]Then[/COLOR]
             c = c + 1
             [COLOR=navy]End[/COLOR] If
           [COLOR=navy]Next[/COLOR] con
 
             [COLOR=navy]If[/COLOR] c = 3 And Application.CountA(Dn.EntireRow) > 0 [COLOR=navy]Then[/COLOR]
                [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]Set[/COLOR] nRng = Range("A" & Dn.Row)
                [COLOR=navy]Else[/COLOR]
                    [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Range("A" & Dn.Row))
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]End[/COLOR] If
c = 0
[COLOR=navy]Next[/COLOR] Dn
'MsgBox nRng.Address
nRng.EntireRow.Delete
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick