How to reference cell style in IF function?
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.
Btw I am referring to how in the "home" tab in excel you can click "cell style" then choose bad, good, neutral or normal!
- garbo7441Lv 76 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
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:
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.
- x-Wulfgar-xLv 66 months ago
Look up conditional formatting.