VBA to Select Cells with Conditional Formats
 

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

VBA to Select Cells with Conditional Formats
 

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)



"todtown" wrote in message
...
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

VBA to Select Cells with Conditional Formats
 

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

ActiveCell.SpecialCells(xlCellTypeAllFormatConditi ons).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



todtown wrote:

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

--

Dave Peterson

VBA to Select Cells with Conditional Formats
 

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
--
Gary''s Student - gsnu200761


"todtown" wrote:

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