• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

recognize color fromat

I have a cell I want to show the answer of 1 or 0. If another cell (N8) is colored or colored red I want cell I want cell AH8 to return zero. Can I do this with the cell function? If so how?

thank you, Bill
0
Billkronmiller
Asked:
Billkronmiller
  • 4
  • 2
1 Solution
 
MacroShadowCommented:
You will have to use VBA.
0
 
BillkronmillerAuthor Commented:
Please help me with that. I have cells N8 and down to N27 and want the results for those cells in AH8 thru AH28
0
 
MacroShadowCommented:
Copy this code (written by byundt @ http://www.vbaexpress.com/kb/getarticle.php?kb_id=190) to a new module:
Function ConditionalColor(rg As Range, FormatType As String) As Long 
     'Returns the color index (either font or interior) of the first cell in range rg. If no _
    conditional format conditions apply, Then returns the regular color of the cell. _ 
    FormatType Is either "Font" Or "Interior" 
    Dim cel As Range 
    Dim tmp As Variant 
    Dim boo As Boolean 
    Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String 
    Dim i As Long 
     
     'Application.Volatile    'This statement required if Conditional Formatting for rg is determined by the _
    value of other cells 
     
    Set cel = rg.Cells(1, 1) 
    Select Case Left(LCase(FormatType), 1) 
    Case "f" 'Font color
        ConditionalColor = cel.Font.ColorIndex 
    Case Else 'Interior or highlight color
        ConditionalColor = cel.Interior.ColorIndex 
    End Select 
     
    If cel.FormatConditions.Count > 0 Then 
         'On Error Resume Next
        With cel.FormatConditions 
            For i = 1 To .Count 'Loop through the three possible format conditions for each cell
                frmla = .Item(i).Formula1 
                If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
                     'Conditional Formatting is interpreted relative to the active cell. _
                    This cause the wrong results If the formula isn 't restated relative to the cell containing the _
                    Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _ 
                    If the Function were Not called using a worksheet formula, you could just activate the cell instead. 
                    frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell) 
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel) 
                    boo = Application.Evaluate(frmlaA1) 
                Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
                    Select Case .Item(i).Operator 
                    Case xlEqual ' = x
                        frmla = cel & "=" & .Item(i).Formula1 
                    Case xlNotEqual ' <> x
                        frmla = cel & "<>" & .Item(i).Formula1 
                    Case xlBetween 'x <= cel <= y
                        frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")" 
                    Case xlNotBetween 'x > cel or cel > y
                        frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")" 
                    Case xlLess ' < x
                        frmla = cel & "<" & .Item(i).Formula1 
                    Case xlLessEqual ' <= x
                        frmla = cel & "<=" & .Item(i).Formula1 
                    Case xlGreater ' > x
                        frmla = cel & ">" & .Item(i).Formula1 
                    Case xlGreaterEqual ' >= x
                        frmla = cel & ">=" & .Item(i).Formula1 
                    End Select 
                    boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
                End If 
                 
                If boo Then 'If this Format Condition is satisfied
                    On Error Resume Next 
                    Select Case Left(LCase(FormatType), 1) 
                    Case "f" 'Font color
                        tmp = .Item(i).Font.ColorIndex 
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.ColorIndex 
                    End Select 
                    If Err = 0 Then ConditionalColor = tmp 
                    Err.Clear 
                    On Error Goto 0 
                    Exit For 'Since Format Condition is satisfied, exit the inner loop
                End If 
            Next i 
        End With 
    End If 
     
End Function 
 
 
Sub NonConditionalFormatting() 
    Dim cel As Range 
    Application.ScreenUpdating = False 
     
     'Remove conditional formatting from entire worksheet
     'For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
    For Each cel In Selection 'Remove conditional formatting from selected cells
        If cel.FormatConditions.Count > 0 Then 
            cel.Interior.ColorIndex = ConditionalColor(cel, "Interior") 'Replace the interior (highlight) color
            cel.Font.ColorIndex = ConditionalColor(cel, "Font") 'Replace the font color
            cel.FormatConditions.Delete 'Delete all the Format Conditions for this cell
        End If 
    Next cel 
     
    Application.ScreenUpdating = True 
End Sub 

Open in new window

Then enter this formula in cell AH8:
=IF(ConditionalColor(N8,"interior")=-4142,0,1)

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
DeleteCommented:
To do this without using VBA is a two step process.

1. On the Excel Ribbon click "Formulas" and then click on "Name Manager".
2. Select "New" and then enter a name such as "ColoredCells".  
3. Jump down to the "Refers to" part and enter the following:

=GET.CELL(63,Sheet1!$N$8)

4. Click OK ot close the Name Manager.
5. In cell AH8 enter the following:

=IF(CellColor=3,"0",IF(CellColor<>3,"1",""))

6. If the cell doesn't automatically update you can either hit F9 to force excel to update the calculations.  If that doesn't always work you can select cell AH8, put you cursor in the formula bar at the end of the formula and hit Enter (this always works).

Note: In the formula you place in AH8, the number 3 relates to the color Red.  Here is a link to all of the colors and their corresponding numbers.  If you want to use a different color just change both 3's to the new number.

http://www.smixe.com/excel-color-pallette.html

Also make sure you are selecting the correct color from the color menu.  Color 3 (Red) when looking in the color palette is the second one from the left under Standard Colors.
0
 
BillkronmillerAuthor Commented:
Works great. Thank you very much, Bill
0
 
BillkronmillerAuthor Commented:
Excero, I am trying to get this to work but having problems. I am using this for cells N8 down to N28 and AH8 to AH28.. I get #NAME? for the answer. =GET.CELL(63,'JOB INFO'!$N$8:$N$28)  is what I have entered in name manager under ColoredCells.

This would be great if I could get it to work. Thank you Bill
0
 
BillkronmillerAuthor Commented:
Also I do have text in cells N8 thru N28
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now