Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    IF statment based on color of Cell (Excel 97/2000)

    Hello..

    I would like to have an If statement which says:

    If cell B2 is highlighted yellow, "Pending", else, ""

    Is it possible to do an if statement based on the highlighting of a cell?

    Thanks!

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF statment based on color of Cell (Excel 97/2000)

    hi awckie

    Don't know what your constraints are, but you could tackle it the other way around, by using Conditional Formatting (under Format on the Main Menu Bar).

    If you format all the cells in Column B to turn yellow, provided the value in Column C(?) is "Pending", you would appear to be able to accomplish the same result.

    HTH
    Gre

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: IF statment based on color of Cell (Excel 97/2000)

    It actually won't work that way...

    I have 600 rows of data. Someone went through and color coded the items based on whether it was pending, completed or in process. No where on any row is this information! So I either have to type the info in myself (which I don't want to do) or find a way to do if statements based on color!

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF statment based on color of Cell (Excel 97/2000)

    As far as I know, you are unable to access the colorindex property without a custom function. The following code will do what you wish however (in column F, cells 1 to 20 - change as you wish):

    <pre>Sub Pend()
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("F1:f20")
    With cell.Interior
    If .ColorIndex = 6 Then cell.Value = "Pending"
    If .ColorIndex <> 6 Then cell.Value = ""
    End With
    Next
    End Sub
    </pre>



    CAUTION: This will overwrite whatever data might happen to be in the cell you are testing for the colorindex. You can change the code to put the "Pending" into another column if you wish.

    You could run it as a result of the sheet activate or deactivate, a button, etc.

    HTH,

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: IF statment based on color of Cell (Excel 97/2000)

    .. or, following Michael Reads' suggestion, here's a custom function which will return the colorindex, so you can process the number returned via =VLOOKUP(), =CHOOSE() or whatever:

    Public Function GetFillColor(rngCell As Range) As Integer
    Application.Volatile
    GetFillColor = rngCell.Interior.ColorIndex
    End Function

    If it's in the same WB or an add-in, usage will be: =getfillcolor(celladdress)
    If it's in Personal.xls, usage will be: =personal.xls!getfillcolor(celladdress)
    -John ... I float in liquid gardens
    UTC -7DS

  6. The Following User Says Thank You to JohnBF For This Useful Post:

    awckie (2011-05-24)

  7. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF statment based on color of Cell (Excel 97/2000)

    You will need a User Defined Function to do this. The following function will return "Pending" if the cell passed as the argument is filled Yellow:

    <pre>Public Function CkColor(oCell As Range) As String
    If oCell.Interior.ColorIndex = 6 Then
    CkColor = "Pending"
    End If
    End Function
    </pre>


    However, if you are changing the cells fill color manually, without changing the cell value, that will not trigger a sheet recalculate. You will have to press F9 after changing the color to trigger the recalculate. If that is a problem, then the following code can be put into the Worksheet Selection Change event routine to cause the recalculate to trigger.

    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(ActiveSheet.Range("C1:C10"), ActiveCell) Is Nothing Then
    ActiveSheet.Calculate
    End If
    End Sub
    </pre>

    Legare Coleman

  8. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: IF statment based on color of Cell (Excel 97/2000)

    John....

    I really like your answer. I used it and it worked great!

    Thanks everyone!

Posting Permissions

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