VBA cacro to find next conditionally highlighted cell

[eldcampo]

I am wondering if anyone knows a way using VBA to cycle through a spreadsheet, finding cells that have been highlighted due to the results of conditional formatting?

The spreadsheet I'll be using it in is quite large and I'd like to make it easier for my users to navigate to each of the highlighted cells.

Please let me know of any ideas!

 

[eldcampo]

And obviously I meant MACRO, not CACRO.

Sorry for the typo!

 

[eldcampo]

Does anyone have thoughts on this one?

 

[VoG]

Maybe like this

Sub atest()
Dim c As Range, r As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
For Each c In r
    c.Select
    MsgBox "Click OK to continue"
Next c
End Sub

 

[eldcampo]

Thanks! That helps go from one conditional cell to the next - is there a way for it to go to the cells that were TRUE and thus were highlighted?

 

[VoG]

That gets really difficult I'm afraid. See http://www.cpearson.com/excel/CFColors.htm

 

[eldcampo]

Thanks very much for your help. Just out of curiosity, do you think we could we revise the following code to apply the highlighting directly to the cells, not through conditional formatting?

With Range("O5:FE500")
        .FormatConditions.Add Type:=xlExpression, Formula1:="=SUMPRODUCT(--(ISNUMBER(SEARCH(Keywords1,RC))))"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 49407
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With
    End With