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

# 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
• 4
• 2
1 Solution

Commented:
You will have to use VBA.
0

Author 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

Commented:
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
``````
Then enter this formula in cell AH8:
``````=IF(ConditionalColor(N8,"interior")=-4142,0,1)
``````
0

Commented:
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 Commented:
Works great. Thank you very much, Bill
0

Author 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

Author 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.