Conditional Formatting

[Gareth]

Is it possible to check all VISIBLE rows on all VISIBLE sheets in a file to
see if there any cells with conditional formatting in them.

I use cond. formatting as follows:

Formula Is =H7="" and a color would be applied to the cell if it was blank
(34). I want to check all visible sheets and visible rows in those sheets
for any empty cells with this cond. format.

Hope this makes sense.

Your help much appreciated as usual.

Gareth

[Bob Phillips]

You could just add a helper column that tests the same condition as the CF,
and this will show TRUE or FALSE. If filtered, it only shows visible cells
anyway.

--

HTH

RP

"Gareth" <[email protected]> wrote in message
news:[email protected]...
> Is it possible to check all VISIBLE rows on all VISIBLE sheets in a file
to
> see if there any cells with conditional formatting in them.
>
> I use cond. formatting as follows:
>
> Formula Is =H7="" and a color would be applied to the cell if it was blank
> (34). I want to check all visible sheets and visible rows in those sheets
> for any empty cells with this cond. format.
>
> Hope this makes sense.
>
> Your help much appreciated as usual.
>
> Gareth
>
>
>

[Alan Hutchins]

I searched on Conditional formatting, and there was a post from 2004 which
seems to answer your question


Sub Tester02()
Dim Rng As Range
Dim myCount As Long

On Error Resume Next
Set Rng = Columns("A:IV").SpecialCells _
(xlCellTypeAllFormatConditions)
On Error GoTo 0

If Not Rng Is Nothing Then
myCount = Rng.Count
Else
myCount = 0
End If
MsgBox myCount

End Sub

I forgot who originally posted it (so please note this is not my code, and
credit should go yto the person who originally wrote it), but it is very very
fast.

If you need to find out where the CF is, then you can amend it to search
through the sheets as you want.

HTH
--
Alan Hutchins


"Gareth" wrote:

> Is it possible to check all VISIBLE rows on all VISIBLE sheets in a file to
> see if there any cells with conditional formatting in them.
>
> I use cond. formatting as follows:
>
> Formula Is =H7="" and a color would be applied to the cell if it was blank
> (34). I want to check all visible sheets and visible rows in those sheets
> for any empty cells with this cond. format.
>
> Hope this makes sense.
>
> Your help much appreciated as usual.
>
> Gareth
>
>
>
>

[JakeyC]

>From Help:

To find all cells that have conditional formatting (conditional format:
A format, such as cell shading or font color, that Excel automatically
applies to cells if a specified condition is true.), click any cell.
To find cells that have conditional formatting settings identical to
the settings of a specific cell, click the specific cell.

On the Edit menu, click Go To.
Click Special.
Click Conditional formats.
Do one of the following:
To find cells with any conditional formatting, click All below Data
validation.

To find cells with identical conditional formats, click Same below Data
validation.