Color problem, excel doesn't recognize color.

[niluj]

I am labeling duplicate cells a certain color like this

Sheets("Duplicatesw").Select
    Columns("E:E").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 8
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Cells.Select

However, once this is done, excel is not recognizing the color.
if i run this sub on one of the cells, it does not recognize the color

Sub recognize()

If ActiveCell.Interior.ColorIndex = 8 Then
MsgBox "its blue"
Else
MsgBox "its not"
End If

End Sub

but when i color the cell with this code, it will recognize the color with the previous code.

Sub makecolor()
ActiveCell.Interior.ColorIndex = 8
ActiveCell.Offset(1, 0).Select
End Sub

Does anyone know what I am doing wrong?

 

[mikerickson]

Color applied by Conditional Formatting is not recognized by looking at the .Interior.ColorIndex property.

To test whether a Conditional Format is applied (resulting in a colored cell), the condition must be probed and investigated.

Which is a problem, because Conditional Formatting changed dramaticaly after 2004.

This works in Excel 2003 and earlier.

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

 

[niluj]

Any ideas on how to recognize the cells that are duplicates after excel found the duplicates?

Is there a way to find the color? or maybe use a different format?

 

[mikerickson]

Dont test for color, test for the condition that caused the color.

 

[niluj]

To test a cell that has been conditionally formatted with this formula:

If ActiveCell.Font.Italic = True Then
MsgBox "yep"
Else
MsgBox "nope"
End If

I get "nope"
When tested with this code:

If ActiveCell.FormatConditions(1).Font.Italic = True Then
MsgBox "yep"
Else
MsgBox "nope"
End If

I get "yep".

Now. If i try to test a font that is italic or non-italic in the conditionally formatted field, i get "yep" either way. So the question is how to tell an italic from a non-italic WITHIN the conditional format.

You say measure it by the condition... i do not understand.

 

[Peter_SSs]

You say measure it by the condition... i do not understand.

With your duplicates issue for example. You have used Conditional Formatting to colour duplicates.

Q. What makes a cell a duplicate?
A. There is more than one of them in the range in question.

So, to test for a duplicate ..

- don't try to test its colour (you've already seen that doesn't work).

- do test the value in the cell to see if there is more that one in the range in question.

That is, to do your test, you are using the original condition that the CF used to decide if the cell should be coloured.
Or in Mike's words:
"Dont test for color, test for the condition that caused the color."

BTW, what are you wanting to do with those duplicates when you 'recognise' them?

 

[niluj]

The goal is to take the entire row of each duplicate and paste them into a new sheet.


So I am assuming the best way is to skip conditional formatting altogether, and create a loop that will look for duplicate values?


What if i were to add a $ character to each duplicate value with conditional formatting? Do you think one could look for the "$ Character?

 

[mikerickson]

Have you looked at Advanced Filter?

Searching this forum should show several threads about removing, segregating, copying or otherwise manipulating duplicate entries.

 

[niluj]

Thanks for the tip. I'll check it out.

It is completely illogical how excel would hide the data after duplicates have been found. It seems like a step backwards.

 

[niluj]

Hey all, here is how I ended up resolving the problem. I skipped use the conditional formatting all together. I just manually went through excel by sorting the column, and if the column was = to the preceding column, I would highlight it.

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A:A")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
Range("a1").Select
Do Until IsEmpty(ActiveCell)
If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
ActiveCell.Interior.ColorIndex = 3
ActiveCell.Offset(1, 0).Interior.ColorIndex = 3
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add(Range("A1"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0 _
        , 0)
    With ActiveSheet.Sort
        .SetRange Range("A:A")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

This places all duplicate values, highlighted in red, sorted at the top of the column.

Avoided conditional formatting. Thank you all who took the time to respond.

God bless.