Conditional format question:turn Cell

[Patrick Simonds]

Using conditional formatting, is there any way to turn Cell A1 red if a
conditional format has been applied to any cells in the range D1:H1?

[Nick Hebb]

You could create a user defined function that checks the range for the
same conditions as the conditional formatting in range D1:H1 checks
for. As a simple example, if the conditional formatting for D1:H1 is
applied for values > 5, the user defined function might look like:

Public Function myUDF(ByRef TargetRange As Range) As Boolean

Dim r As Range
For Each r In TargetRange.Cells
If r.Value > 5 Then
myUDF = True
Exit Function
End If
Next
myUDF = False

End Function


Then in A1's conditional formatting, select "Formula is" from the
dropdown and enter =myUDF(D1:H1) and select the formatting for a TRUE
condition.

Would this work for you?

Nick Hebb
BreezeTree Software
http://www.breezetree.com

[Norman Jones]

Hi Patrick,

Try using the following UDF as the CF condition for cell A1:

'================>>
Public Function CFApplied(rng As Range) As Boolean
Dim rCell As Range

For Each rCell In rng.Cells
If rCell.FormatConditions.Count Then
CFApplied = True
Exit For
End If
Next rCell
End Function
'<<================

The CF condition for A1 would then be:

Formula Is: =CFApplied($D$1:$H1)

---
Regards,
Norman


"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:eMxG0sIzFHA.3812@TK2MSFTNGP09.phx.gbl...
> Using conditional formatting, is there any way to turn Cell A1 red if a
> conditional format has been applied to any cells in the range D1:H1?
>

[Nick Hebb]

Norman, I believe that function will return TRUE even if the
conditional formating in D1:H1 is not applied.

Nick Hebb
BreezeTree Software
http://www.breezetree.com

[Norman Jones]

Hi Nick,

> Norman, I believe that function will return TRUE even if the
> conditional formating in D1:H1 is not applied.

Running:
'===========>>
Sub Testit()
Dim RngTest As Range

Set RngTest = Range("D1, F1, H1")

With RngTest

.FormatConditions.Delete

.FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="100"
Debug.Print CFApplied(RngTest), "Some CF"

.FormatConditions.Delete

Debug.Print CFApplied(RngTest), "No CF"
End With

End Sub
'===========>>

Returned:

True Some CF
False No CF

for me.

---
Regards,
Norman


"Nick Hebb" <n.hebb@comcast.net> wrote in message
news:1128848365.241533.301690@g47g2000cwa.googlegroups.com...
> Norman, I believe that function will return TRUE even if the
> conditional formating in D1:H1 is not applied.
>
> Nick Hebb
> BreezeTree Software
> http://www.breezetree.com
>

[Peter T]

If you mean -

a) The range has been applied with CF's and you want to know if any of the
cells show the CF format because of a true condition, see Nick's suggestion.

or

b) You want to know if CF's have been applied to any cells in the range,
regardless as to whether any conditions are true and one or more CF formats
display, see Norman's suggestion.

The latter is the correct literal interpretation of your question. But
somehow I suspect you mean the former. (?)

Regards,
Peter T


"Patrick Simonds" <ordnance1@comcast.net> wrote in message
news:eMxG0sIzFHA.3812@TK2MSFTNGP09.phx.gbl...
> Using conditional formatting, is there any way to turn Cell A1 red if a
> conditional format has been applied to any cells in the range D1:H1?
>
>