Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting based on cell color (Prof. 2003)

    I am trying to do a formula that basically says if cell a2 is filled the color yellow than return true, otherwise false. I know how to do if statements, but I'm not sure how to do it for the formating of the cell. I know conditional formatting will fill the color of the cell for me based on it's contents. But I want the reversal. Also, the cell that is filled yellow has content in it that can't be replaced--and in that column even though the cells are yellow, then content is not identical-so I can't do a formula based on the content. Thank you!!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting based on cell color (Prof. 2003)

    See Chip Pearson's Functions For Working With Cell Colors. These functions with colors assigned through the Format | Cells dialog or the Formatting toolbar.

    If you want to work with colors assigned by Conditional Formatting, see Conditional Formatting Colors on the same site.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting based on cell color (Pr

    Hi Christina

    You could put this in a module:

    Function CellColour(InRange As Range) As String


    If InRange(1, 1).Interior.ColourIndex = 6 Then

    CellColour = "True"

    Else

    CellColour = "False"


    End If

    End Function
    Jerry

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting based on cell color (Pr

    A formula way by using Marco-4 GETCELL

    Try……

    1] Activate Insert | Name | Define
    In the "Names box", entered :
    CellColor
    In the "Refers to" box, entered :
    =GET.CELL(38,INDIRECT("rc[-1]",FALSE))+0*NOW()

    2] In Cell A2, filled with yellow color

    3] In Cell B2, entered the folmula :

    =IF(CellColor=6,TRUE,FALSE)

    Regards
    Bosco

  5. #5
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting based on cell color (Pr

    Thank you very much!!! That was perfect!!! It worked!
    Now, in order for it to always work....does my "if" function always need to be in the very next column to the right of the column containing the fill colors?
    Again, thank you all for your help!!!!!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Formatting based on cell color (Pr

    The defined name in Bosco_Yip's reply always refers to the cell immediately to the left of the cell in which you use the name.
    The function described by Jezza takes the cell you want to refer to as argument, so you can use the function anywhere you want, even on another worksheet.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional Formatting based on cell color (Pr

    Referring to the cell address under the defined name formula :

    =GET.CELL(38,INDIRECT("rc[-1]",FALSE))+0*NOW()

    The cell address is using "R1C1 reference style", you can change in this part ……."rc[-1]" in order to suit your requested cell location

    Details of the "R1C1 reference style", please refer to the "About cell and range references" under the Excel Help file

    Regards
    Bosco

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •