Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Check Conditional Formatting

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello
    I Know this must have been arswered many times before, but I can't find it.

    Is it possible in VBA to check if cell meets the given conditions for formatting.

    ex. if a range of cells have been formatted to be interior.colorindex=15 (grey) if they are above 10, is it then possible to check the color in vba??

    I've tried to make a makro that would only select the cells in an area that meet the conditions, but I failed somewhere.

    Sub Macro2()
    Dim Same As Range
    Firsttime = True
    test = Selection.Interior.PatternColorIndex
    Selection.SpecialCells(xlCellTypeSameFormatConditions).Select
    For Each c In Selection.Cells
    If c.Interior.PatternColorIndex <> test Then
    If Firsttime = True Then
    Set Same = Range(c.Address)
    Firsttime = False
    Else: Set Same = Application.Union(Range(c.Address), Same)
    End If
    End If
    Next
    Same.Select
    End Sub

    TIA Tommy

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What is happening here??
    No repplies at all.
    is this to hard a question?? or are you missing some info?+

    regards Tommy

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not sure what you're asking.

    Are you wanting to evaluate the color shading of a cell to make sure it is (in your example) gray-shaded if its numeric value is greater than 10? If so, why not start from scratch by simply unshading all the cells in your range, conditionally format that range for the cell color to be shaded gray if it contains a 10 or higher, and that's that.

    Alternatively, you could AutoFilter the range to display only cells in that range which contain a number higher than 10, then select the filtered column(s) you want to shade, and click on Format > Cells > Patterns tab to choose the #15 gray index square from the palette. Remove the filter and you're good to go.

    Both approaches avoid VBA to keep things simple.

    But, are you asking for VBA to evaluate the color index of each cell? And if so, index 15 gray in particular? If so, try this macro...modify it for column of interest (assumes A contains the numbers/shading), and the column for the evaluation return text (code uses column D):

    Sub GrayPatrol()
    Dim cell As Range
    Dim rG As Range
    Set rG = Range([A2], [A65536].End(xlUp))
    rG.Offset(, 3).Clear
    For Each cell In rG
    If cell.Interior.ColorIndex = 15 Then
    cell.Offset(, 3).Value = "Yes, gray!!"
    End If
    Next
    End Sub

    Keep in mind that with conditional formatting, Excel does not regard the formatted cell as containing a certain color index, so this VBA evaluation would not work for conditionally formatted cells, but would work with actually formatted cells, per the first option I suggested above.

    Regarding VBA, there have also been UDFs posted on this board to evaluate a color index by entering a formula that could return any color index number, and the macro above could do so also with modifications. You could locate them by using the Search feature on this board.

    Hopefully though, this info might get you started.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Keep in mind that with conditional formatting, Excel does not regard the formatted cell as containing a certain color index, so this VBA evaluation would not work for conditionally formatted cells,
    Thank you very much, Tom.
    It was a very good answer.
    The above quote was exactly what I was looking for. So what I was trying to do was impossible. I just needed to get that confirmed, so i could stop thinking about what I did wrong.

    Regards Tommy

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
  •