Solved

recognize color fromat

Posted on 2014-03-29
7
311 Views
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
0
Comment
Question by:Billkronmiller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39964157
You will have to use VBA.
0
 

Author Comment

by:Billkronmiller
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
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 250 total points
ID: 39964172
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 7

Expert Comment

by:Delete
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:

=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
 

Author Closing Comment

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

Author Comment

by:Billkronmiller
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
0
 

Author Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 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