How to reference cell style in IF function?

Hi,

If i want to make an IF function like:

If the cell style is "good," value = 1, how would I do this? Not sure how to put the cell style in the formula.

Thanks!

Update:

Btw I am referring to how in the "home" tab in excel you can click "cell style" then choose bad, good, neutral or normal!

2 Answers

Relevance
  • 6 months ago

    Here is one way to do as you wish using a formula. However, you will have to create a UDF (User Defined Function) in VBA first. Quite simple to do.

    Open your workbook and press ALT + F11 (Function Key F11).

    In the menus at the top of the VBE, select 'Module' in the Insert drop down.

    Copy and paste this function in the white editing area to the right in the new module:

    Function cStyle(c As Range) As String

    cStyle = c.Style

    End Function

    Close the VBE (red button - top right)

    Save the workbook as an Excel Macro-Enabled Workbook to retain this functionality in the future. Archive, or delete, the original version to avoid confusion.

    Assuming you are evaluating A1, use this formula in the cell of your choice:

    =IF(cSstyle(A1)="Good",1, IF(cStyle(A1)="Bad",2, IF(cStyle(A1)="Neutral",3,"")))

    If you just want to evaluate for 'Good', use:

    =IF(cStyle(A1)="Good",1,"")

    Note: The caveat here is that it does not appear to be dynamic. If the style changes after the formula is initially applied, you must recalculate the sheet (Ctrl + ALT + F9).

    It is possible to expand the possibilities of data manipulation based on cell styles through the use of VBA event handlers or macros, rather than using a limiting UDF. For example, you could sum the contents of all cells displaying the style 'Good'. Or sum the contents of cells offset in the same row as the cells displaying 'Good' style.

  • 6 months ago

    Look up conditional formatting.

    • garbo7441
      Lv 7
      6 months agoReport

      Formulas cannot parse conditionally applied formatting. Neither can VBA.

Still have questions? Get your answers by asking now.