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

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » IF statment based on color of Cell (Excel 97/2000)

    Author
    Topic
    #364096

    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!

    Viewing 2 reply threads
    Author
    Replies
    • #557821

      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

      • #557822

        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!

        • #557827

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

          • #557831

            John….

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

            Thanks everyone!

    • #557824

      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):

      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
      

      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,

    • #557829

      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:

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

      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.

      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
      
    Viewing 2 reply threads
    Reply To: IF statment based on color of Cell (Excel 97/2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: