Formats in formulas

[Paul]

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

 

[Arvi Laanemets]

Hi

Use an UDF to return format value.


Arvi Laanemets

 

[Shane Devenshire]

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

 

[Satti Charvak]

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