If (cell color) equals, then...

[klynshoe]

Hello,

I have some cells under conditional formatting rules to highlight it a certain color. In another worksheet, I want it to pull a particular value if that cell color is highlighted. How would I go about doing this?

~~~~~~~
=if(F5=ORANGE,"",D6)
~~~~~~~

USING EXCEL 2010

 

[Snakehips]

klynshoe,

I think you will find that you cannot relate to the conditional colour of a cell.
So you will have to substitute F5 = Orange either directly with the same CF test that you use to determine the colour of F5 or indirectly by some other means.

Hope that helps.

 

[Kenneth Hobson]

It would be easier to use the conditional formula to determine if the condition was true.

Use this method to first find the Interior Color or Interior Color Index needed for your IF() formula to replace "Green". Then add that part needed to your IF().

Sub ken()
  Dim r As Range
  Set r = Worksheets("Sheet1").Range("F5")
  '=DisplayedColor(F5, True, False)
  MsgBox DisplayedColor(r, True, False), vbInformation, "Interior Color"
  '=DisplayedColor(F5)
  MsgBox DisplayedColor(r), vbInformation, "Interior Color Index"
End Sub

' Rick Rothstein
' http://www.excelfox.com/forum/f22/get-displayed-cell-color-whether-from-conditional-formatting-or-not-338/index2.html

' Arguments
' ----------------
' Cell - Required Range, not a String value, for a **single** cell
'
' CellInterior - Optional Boolean (Default = True)
'                True makes function return cell's Interior Color or ColorIndex based on
'                the ReturnColorIndex argument False makes function return Font's Color or
'                ColorIndex based on the ReturnColorIndex argument
'
' ReturnColorIndex - Optional Boolean (Default = True)
'                    True makes function return the ColorIndex for the cell property determined
'                    by the CellInterior argument False make function return the Color for the
'                    cell property determined by the CellInterior argument
'
Function DisplayedColor(Cell As Range, Optional CellInterior As Boolean = True, _
                        Optional ReturnColorIndex As Long = True) As Long
  Dim X As Long, Test As Boolean, CurrentCell As String
  If Cell.Count > 1 Then Err.Raise vbObjectError - 999, , "Only single cell references allowed for 1st argument."
  CurrentCell = ActiveCell.Address
  For X = 1 To Cell.FormatConditions.Count
    With Cell.FormatConditions(X)
      If .Type = xlCellValue Then
        Select Case .Operator
          Case xlBetween:      Test = Cell.Value >= Evaluate(.Formula1) And Cell.Value <= Evaluate(.Formula2)
          Case xlNotBetween:   Test = Cell.Value <= Evaluate(.Formula1) Or Cell.Value >= Evaluate(.Formula2)
          Case xlEqual:        Test = Evaluate(.Formula1) = Cell.Value
          Case xlNotEqual:     Test = Evaluate(.Formula1) <> Cell.Value
          Case xlGreater:      Test = Cell.Value > Evaluate(.Formula1)
          Case xlLess:         Test = Cell.Value < Evaluate(.Formula1)
          Case xlGreaterEqual: Test = Cell.Value >= Evaluate(.Formula1)
          Case xlLessEqual:    Test = Cell.Value <= Evaluate(.Formula1)
        End Select
      ElseIf .Type = xlExpression Then
        Application.ScreenUpdating = False
        Cell.Select
        Test = Evaluate(.Formula1)
        Range(CurrentCell).Select
        Application.ScreenUpdating = True
      End If
      If Test Then
        If CellInterior Then
          DisplayedColor = IIf(ReturnColorIndex, .Interior.ColorIndex, .Interior.Color)
        Else
          DisplayedColor = IIf(ReturnColorIndex, .Font.ColorIndex, .Font.Color)
        End If
        Exit Function
      End If
    End With
  Next
  If CellInterior Then
    DisplayedColor = IIf(ReturnColorIndex, Cell.Interior.ColorIndex, Cell.Interior.Color)
  Else
    DisplayedColor = IIf(ReturnColorIndex, Cell.Font.ColorIndex, Cell.Font.Color)
  End If
End Function

Or more simply:

MsgBox Range("F5").DisplayFormat.Interior.Color
MsgBox Range("F5").DisplayFormat.Interior.Colorindex