recognize color fromat

Posted on 2014-03-29
Last Modified: 2014-03-29
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
Question by:Billkronmiller
  • 4
  • 2
LVL 27

Expert Comment

ID: 39964157
You will have to use VBA.

Author Comment

ID: 39964177
Please help me with that. I have cells N8 and down to N27 and want the results for those cells in AH8 thru AH28
LVL 27

Accepted Solution

MacroShadow earned 250 total points
ID: 39964172
Copy this code (written by byundt @ 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 
                    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:

Open in new window

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Expert Comment

ID: 39964180
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:


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


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.

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.

Author Closing Comment

ID: 39964188
Works great. Thank you very much, Bill

Author Comment

ID: 39964260
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

Author Comment

ID: 39964261
Also I do have text in cells N8 thru N28

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question