Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formats in formulas
Hey there,
is there any way in which logical test used in a formula could be, for example, "if(valueA=red text, value if true, value if false)" or is there some other way in which the formatting of a value can influence output Sort of like the opposite of conditional formatting? Any help would be appreciated, Paul :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formats in formulas
Hi
Use an UDF to return format value. Arvi Laanemets "Paul" wrote in message ... Hey there, is there any way in which logical test used in a formula could be, for example, "if(valueA=red text, value if true, value if false)" or is there some other way in which the formatting of a value can influence output Sort of like the opposite of conditional formatting? Any help would be appreciated, Paul :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formats in formulas
Hi,
You would need to use VBA. Here is a simple example Here is a custom function to count cells base on font color and fill color: Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Application.Volatile Set S = E.Cells(1, 1) Total = 0 For Each cell In R With cell If .Interior.ColorIndex = S.Interior.ColorIndex _ And .Font.ColorIndex = S.Font.ColorIndex Then Total = Total + 1 End If End With Next cell CountFormats = Total End Function In the spreadsheet you enter =countformats(A2:B7,D2) Where A2:B7 is the range you want to check and D2 is a cell formatted to the desired format. To add this code to a workbook press Alt+F11 and select your file in the Project explorer in the top left side of the screen. Choose Insert, Module. Put the code in the resulting module. If this helps, please click the Yes button Cheers, Shane Devenshire "Paul" wrote: Hey there, is there any way in which logical test used in a formula could be, for example, "if(valueA=red text, value if true, value if false)" or is there some other way in which the formatting of a value can influence output Sort of like the opposite of conditional formatting? Any help would be appreciated, Paul :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formats in formulas
Hi,
you can use the formula: =CELL("color",A1) this would return a value 1, if cell font (only) is done with a color like #,##0_);[Red](#,##0) --- (negative numbers in red color and parenthesis) ;[Red]($#,##0.00); ---(negative numbers in red color and parenthesis) [Green]#,##0_); ---(positive numbers in green color) one point to note is that there should be one ;(semicolon) in the format -- Please Click yes if this post was useful. Kind Regards, Satti Charvak Only an Excel Enthusiast "Paul" wrote: Hey there, is there any way in which logical test used in a formula could be, for example, "if(valueA=red text, value if true, value if false)" or is there some other way in which the formatting of a value can influence output Sort of like the opposite of conditional formatting? Any help would be appreciated, Paul :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can formats follow formulas? | Excel Worksheet Functions | |||
What's the best way to add a row and copy formulas and formats? | Excel Discussion (Misc queries) | |||
Extend formats and formulas | Excel Discussion (Misc queries) | |||
Cell Formats in formulas | Excel Discussion (Misc queries) | |||
extend data range formats and formulas | Setting up and Configuration of Excel |