Solved

recognize color fromat

Posted on 2014-03-29
7
301 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
  • 4
  • 2
7 Comments
 
LVL 26

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 26

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now