Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (2000 SP3)

    Hello -

    I have a number of cells in a worksheet that have conditional formats. Is there any way to write a vba procedure that could review the cells and find only those in which the conditional format has been met? In other words, if my conditional format changes the font color of a cell to red if condition A is met, then can the vba procedure review the cells to see which ones are red because condition A has been met? I'd like to avoid writing all the conditions in my vba code.

    Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting (2000 SP3)

    This function won't find colors changed by CondFormat only EXPLICITLY defined fill colors.

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Conditional Formatting (2000 SP3)

    <P ID="edit" class=small>(Edited by JohnBF on 09-Jul-03 11:46. As Steve notes, the Function originally supplied does not work with formats set via conditional formatting.)</P>See <!post=this,270598>this<!/post> post and thread. It may be easier for you to re-write the conditional test as a formula in another column to test for the condition.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Formatting (2000 SP3)

    Hi,
    This is by no means perfect - in some quick testing it seems to occasionally have problems with expression conditions (it doesn't always find them first time)- but it should get you started:
    <pre>Sub SelectCondFmtsMet()
    Dim rngSearch As Range, rngFound As Range, rngCell As Range
    Dim lngIndex As Long, fcoFCond As FormatCondition
    Dim strOperator As String, strFormula As String
    Dim blnCondTrue As Boolean
    On Error Resume Next
    Set rngSearch = ActiveSheet.Cells.SpecialCells _
    (xlCellTypeAllFormatConditions)
    On Error GoTo err_handler
    If Not rngSearch Is Nothing Then
    For Each rngCell In rngSearch
    For lngIndex = 1 To rngCell.FormatConditions.Count
    Set fcoFCond = rngCell.FormatConditions(lngIndex)
    With fcoFCond
    Select Case .Type
    Case xlCellValue
    Select Case .Operator
    Case xlBetween
    blnCondTrue = Evaluate("AND(" & rngCell.Value & ">" & _
    .Formula1 & ", " & rngCell.Value & "<" & .Formula2 & ")")
    Case xlEqual
    blnCondTrue = Evaluate(rngCell.Value & " = " & .Formula1)
    Case xlGreater
    blnCondTrue = Evaluate(rngCell.Value & " > " & .Formula1)
    Case xlGreaterEqual
    blnCondTrue = Evaluate(rngCell.Value & " >= " & .Formula1)
    Case xlLess
    blnCondTrue = Evaluate(rngCell.Value & " < " & .Formula1)
    Case xlLessEqual
    blnCondTrue = Evaluate(rngCell.Value & " <= " & .Formula1)
    Case xlNotBetween
    blnCondTrue = Evaluate("OR(" & rngCell.Value & "<" & _
    .Formula1 & ", " & rngCell.Value & ">" & .Formula2 & ")")
    Case xlNotEqual
    blnCondTrue = Evaluate(rngCell.Value & " <> " & .Formula1)
    End Select
    Case xlExpression
    blnCondTrue = Evaluate(Mid$(.Formula1, 2))
    End Select
    End With
    If blnCondTrue Then
    If rngFound Is Nothing Then
    Set rngFound = rngCell
    Else
    Set rngFound = Union(rngFound, rngCell)
    End If
    Exit For
    Else
    End If
    Set fcoFCond = Nothing
    Next lngIndex
    Next rngCell
    Else
    MsgBox "No conditionally formatted cells in sheet!"
    End If
    rngFound.Select

    err_handler:

    On Error Resume Next
    Set rngSearch = Nothing
    Set rngFound = Nothing
    Set fcoFCond = Nothing
    End Sub
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •