VBA to Select Cells with Conditional Formats

[todtown]

I have been given a workbook that has conditional formats ALL
throughout. I want to see which cells have them so I can decide to
keep them, turn them off, change them, etc. Is there a way similar to
SpecialCells that I can use to select all cells with Conditional
Formatting set?

tod

 

[Bob Phillips]

No, you need to loop them

Dim rng As Range
Dim cell As Range
Dim fcType

For Each cell In Selection
fcType = Empty
On Error Resume Next
fcType = cell.FormatConditions(1).Type
On Error GoTo 0
If Not IsEmpty(fcType) Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell
If Not rng Is Nothing Then rng.Select


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

I recorded a macro when I used:
edit|goto|Special|conditional formats
and got this one liner:

ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Select

It could use a few checks:

Dim myRng as range
dim wks as worksheet
set wks = activesheet
with wks
set myrng = nothing
on error resume next
set myrng = .cells.SpecialCells(xlCellTypeAllFormatConditions)
on error goto 0
end with

if myrng is nothing then
msgbox "no cells with CF"
else
myrng.select
'or
application.goto myrng, scroll:=true
end if

 

[Gary''s Student]

How about:

Sub formatter()
Set rcon = Nothing
For Each r In ActiveSheet.UsedRange
If r.FormatConditions.Count > 0 Then
If rcon Is Nothing Then
Set rcon = r
Else
Set rcon = Union(rcon, r)
End If
End If
Next

If rcon Is Nothing Then
Else
rcon.Select
End If
End Sub