Results 1 to 6 of 6

Thread: Use color in an if statement

  1. #1

    Use color in an if statement



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I want to write =If(Sheet1!A2=Yellow,0,Sheet1!A2). That is, I am copying data from sheet 1 to sheet 2. I want to be able to invalidate a few cells by manually coloring them. Therefore, I want all cells that I manually color in sheet1 to show 0 in the new cell. Conversely if sheet1!A2 is not colored and contains a numeric value of 6, I want the new cell to show 6.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You cannot use just a formula to check the colour of a cell.

    You can use a VBA UDF though.

    If you hit CTRL+F11 and then INSERT|MODULE, paste the following code in the editor

    Code:
    Function CellColor(CColor As Range) As Integer
        CellColor = CColor.Interior.ColorIndex
    End Function
    Then back in the sheet you need to use something like:

    =If(CellColor(Sheet1!A2)=6,0,Sheet1!A2)

    where 6 refers to interior color code for Yellow.

    To find out the colour code you can use =CellColor(A2) this will tell you the number associated with the colour, then you can use that in your IF conditional check.

    One thing to note with this option is, that if you change the colour in Sheet1!A2, the formula will not update. You will need to go the formula and re-activate and hit enter.


  3. #3
    Your micro worked great. I found a problem. I can detect yellow cells and set a copy in another cell to zero. That was my goal. Unfortunately, it appears that the equation with the micro requires refreshing. That is, if I color another cell after the macro is in place, the micro formula does not respond. If I remove the yellow color, the micro does not respond. I can only get a response if I change the color and then rewrite the macro or paste the macro. That is, it appears the micro formula above must be refreshed. It does not respond as a simple formula. I am I experiencing what you would expect?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I did mention that in the last line of my post:

    One thing to note with this option is, that if you change the colour in Sheet1!A2, the formula will not update. You will need to go the formula and re-activate and hit enter.
    You can add an Application.Volatile True line to the code, which will autochange the result, but with something like color choosing (or data validation), I think you need to use F9 to re-evaluate or after some other physical keyboard entry change occurs.


    Code:
    Function CellColor(CColor As Range) As Integer
        Application.Volatile True
        CellColor = CColor.Interior.ColorIndex
    End Function
    Edit:

    One way to "auto recalc" is to add a Worksheet_SelectionChange event macro:

    right click on the sheet tab, then select View Code. Paste the following:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculate
    End Sub
    Now in the sheet, once you change a color and hit enter, the formula should update.
    Last edited by NBVC; 2014-06-13 at 01:26 PM.


  5. #5
    It works. Thank you.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You're welcome. Notice that I just added an Edit to my last thread, at same time as you posted your last post.


Tags for this Thread

Posting Permissions

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