Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: VBA Code to find Interior Color set by Conditional Formatting

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,507
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to find Interior Color set by Conditional Formatting

    Hi All:

    I have a spreadsheet where I have set up Conditional Formatting in some cells. IF certain criteria are not met then the cell turns red
    .ColorIndex = 3

    Is there any VBA Code that can detect interior color that was set up by Conditional Formatting. I have been seraching the internet for quite a while and found the code below which I modified slightly. It works if I set the interior color to RED via formatting but it ddoes not seem to recognize the RED interior when it is set up through COnditional Formatting. Can this code be modified to work for me? Is ther another code I could use? I would appreciate even if someone can tell me "Not Possible". THANKS.

    Code:
     
    Sub FindRedInterior()
    Dim FoundCell As Range
     
    'just in case there's other stuff that's been specified
    Application.FindFormat.Clear
    Application.FindFormat.Interior.ColorIndex = 3
     
    With ActiveSheet
    Set FoundCell = .Range("Revenue_Distribution").Find(What:="", _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=True)
    If FoundCell Is Nothing Then
    Exit Sub
     
    Else
    
    Application.Goto FoundCell, Scroll:=True
    MsgBox prompt:="Please fill in any cells highlighted in RED " _
    & "and then click on the Email Button again" _
    & vbNewLine & "Start with: " _
    & FoundCell.Address(0, 0), _
    Title:="Jrnl 1 Corrections"
     
    End If
    End With
    End Sub
    ANY Suggestions

    THANKS for looking,
    Take Care,
    Mark

  2. #2
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    As much as I have reserached, I have never came across anything that can give you the color of something formatted by conditional formatting.

    From experience you have to build logic into the code that is the same as your conditional formatting if you want to use that in your code.

    Even though that is probably not what you wanted to hear.
    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sudbury, Ontario Canada
    Posts
    1,507
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    Hi schielrn:

    THANKS

    Definately NOT what I wanted to hear but at least now I know...

    Would it be the same answer if the formatting put in a word instead of or as well as a color? Probably so, because it is still conditional formatting. Correct?

    When I found the code I posted I thought I was onto something. It will definately take a lot longer to write all the conditional formatting into my VBA. I can just do a Fill Down

    Oh well, back to the Drawing Board...

    Take Care,
    Mark

  4. #4
    New Member
    Join Date
    Jul 2012
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    Hi. Just found this thread and am getting to the point where I have more to contribute with Excel expertice. I stumbled on this code that works for Excel 2010, but NOT Excel 2007...hence my search. The following code will check the conditional background color of a cell.

    If ActiveCell.DisplayFormat.Interior.Color = "16777215" Or ActiveCell.DisplayFormat.Interior.Color = "65280" Then
    SendKeys "%{DOWN}"
    End If

    If you find what works for Excel 2007, please post it.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,533
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    25 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    Quote Originally Posted by Mister H View Post
    Hi All:

    I have a spreadsheet where I have set up Conditional Formatting in some cells. IF certain criteria are not met then the cell turns red
    .ColorIndex = 3

    Is there any VBA Code that can detect interior color that was set up by Conditional Formatting.
    I see this is an old thread, but for those who might trip across it, either now or in the future, perhaps the function (cannot be used as a UDF though) that I posted in my mini-blog article here would be of use...

    Get Displayed Cell Color (whether from Conditional Formatting or not)

    Make sure to read Message #1 first as it sets the stage for the code, but then make sure to read Message #2 for a minor revision to that code. Also make sure to read the note appearing after the code block in Message #2.
    Last edited by Rick Rothstein; Aug 19th, 2012 at 03:07 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    Chip Pearson has info about this at his site:

    http://www.cpearson.com/Excel/CFColors.htm
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  7. #7
    New Member
    Join Date
    Jul 2012
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    I loaded this up hoping it would return the correct values and it seems to be returning values for the standard pre-condition format. It does not change the values returned on cells that are different colors based on the conditional format expression. It looks like it should work. I am using color variations for both the background fill and foreground text in the conditional formats. I suspect this is related to the comment about more than one change causes it to fail.

    Thanks for the help.

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,019
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    This worked in Excel 2004 (and 2003?) but not afterwards.

    Code:
    Sub convertSheet()
        Dim sheetToConvert As Worksheet
        Dim rngConditionalFormatted As Range
        Dim uiRange As Range
        
        On Error Resume Next
        Set uiRange = Application.InputBox("Select a Worksheet to convert", Type:=8)
        On Error GoTo 0
        If uiRange Is Nothing Then Exit Sub
        If MsgBox("Convert " & Trim(Left(Application.Substitute(uiRange.Address(, , , True), "$", String(255, " ")), 255)) & " ?", _
                vbYesNo) = vbNo Then Exit Sub
        Set sheetToConvert = uiRange.Parent
        'Set sheetToConvert = ThisWorkbook.Sheets("Sheet1"): Rem adjust
        
        On Error Resume Next
        Set rngConditionalFormatted = sheetToConvert.Cells.SpecialCells(xlCellTypeAllFormatConditions)
        On Error GoTo 0
        
        If Not rngConditionalFormatted Is Nothing Then
            Call ConvertCFtoBaseFormat(rngConditionalFormatted)
        Else
            MsgBox "no cf"
        End If
    End Sub
    
    Sub ConvertCFtoBaseFormat(rangeToConvert As Range)
        Dim oneCell As Range
        Application.ScreenUpdating = False
        For Each oneCell In rangeToConvert
            If CFormatMet(oneCell) <> 0 Then
                Call CopyCFto(oneCell, CFormatMet(oneCell))
            End If
            oneCell.FormatConditions.Delete
        Next oneCell
        Application.ScreenUpdating = True
    End Sub
    
    Sub CopyCFto(sourceCell As Range, CFConditionMet As Long, Optional destinationCell As Range)
        Rem Set destination cell base formatting = source cell conditional format #
        If destinationCell Is Nothing Then Set destinationCell = sourceCell.Cells(1, 1)
        Dim anEdge As Variant
        With sourceCell.Cells(1, 1).FormatConditions(CFConditionMet)
            With .Font
                If Not IsNull(.Bold) Then destinationCell.Font.Bold = .Bold
                If Not IsNull(.Italic) Then destinationCell.Font.Italic = .Italic
                If Not IsNull(.Underline) Then destinationCell.Font.Underline = .Underline
                If Not IsNull(.Strikethrough) Then destinationCell.Font.Strikethrough = .Strikethrough
                If Not IsNull(.ColorIndex) Then destinationCell.Font.ColorIndex = .ColorIndex
            End With
            
            On Error Resume Next
            For Each anEdge In Array(xlLeft, xlRight, xlTop, xlBottom)
                With .Borders(anEdge)
                    If Not IsNull(.LineStyle) Then destinationCell.Borders(anEdge).LineStyle = .LineStyle
                    If Not IsNull(.Weight) Then destinationCell.Borders(anEdge).Weight = .Weight
                    If Not IsNull(.ColorIndex) Then destinationCell.Borders(anEdge).ColorIndex = .ColorIndex
                End With
            Next anEdge
            On Error GoTo 0
            
            With .Interior
                If Not IsNull(.PatternColorIndex) Then _
                        destinationCell.Interior.PatternColorIndex = .PatternColorIndex
                If Not IsNull(.Pattern) Then destinationCell.Interior.Pattern = .Pattern
                If Not IsNull(.ColorIndex) Then destinationCell.Interior.ColorIndex = .ColorIndex
            End With
        End With
    End Sub
    
    Function CFormatMet(oneCell As Range) As Long
        Rem which of the three conditional formatting conditions is met
        Rem given a cell, returns the number of the conditional format condtion that is met
        Rem if CF not engaged, returns 0
        Dim testFormula As String, tempFormula As String
        Dim i As Long
            With oneCell
                For i = 1 To .FormatConditions.Count
                    testFormula = .Value
                    If testFormula = vbNullString Then testFormula = 0
                    With .FormatConditions(i)
                        If .Type = xlCellValue Then
                            Select Case .Operator
                                Case xlBetween
                                    testFormula = "AND(" & .Formula1 & "<=" & testFormula & "," & testFormula & "<=" & .Formula2 & ")"
                                Case xlNotBetween
                                    testFormula = "NOT(AND(" & .Formula1 & "<=" & testFormula & "," & testFormula & "<=" & .Formula2 & "))"
                                Case xlEqual
                                    testFormula = testFormula & "=" & .Formula1
                                Case xlGreater
                                    testFormula = testFormula & ">" & .Formula1
                                Case xlGreaterEqual
                                    testFormula = testFormula & ">=" & .Formula1
                                Case xlLess
                                    testFormula = testFormula & "<" & .Formula1
                                Case xlLessEqual
                                    testFormula = testFormula & "<=" & .Formula1
                                Case xlNotEqual
                                    testFormula = testFormula & "<>" & .Formula1
                            End Select
                        Else
                            tempFormula = Application.ConvertFormula(.Formula1, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)
                            testFormula = Application.ConvertFormula(tempFormula, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1, _
                                                    ToAbsolute:=True, RelativeTo:=oneCell)
                        End If
                    End With
                    If Evaluate(testFormula) Then Exit For
                Next i
            End With
            CFormatMet = i Mod (oneCell.FormatConditions.Count + 1)
    End Function

  9. #9
    New Member
    Join Date
    Jul 2012
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    Hi Chip, Thanks for the link. I loaded up all of the functions and each one with all options still gives no difference between a conditional formatted cell and one that does not meet the conditions. Of the requirements listed:

    • You are calling ActiveCondtion from a worksheet cell,
      AND
    • The cell passed to ActiveCondtion uses a "Formula Is"
      rather than
      "Cell Value Is" condition, AND
    • The formula used in the condition formula contains relative
      addresses



    I'm using "Cell value is", but the condition formula does use relative addresses. The code I used above for excel 2010 works fine, but the ActiveCell.DisplayFormat.Interior.Color does not work in 2007.

  10. #10
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,011
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: VBA Code to find Interior Color set by Conditional Formatting

    It's a lot easier to put the relevant CF formula in a cell and use that for both CF and counting.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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