Color

[Angeles]

Is is possible to define a condition depending of the text color of cells ?

Thank you

 

[ryguy7272]

This may give you what you want:
Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = 3)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

Code is from:
http://www.cpearson.com/excel/colors.htm

Call it in the following manner:
=CountByColor(A1:F5,3,TRUE)
Notice, this is for text colored red, which Excel recognizes as 3.

Regards,
Ryan---

 

[Gord Dibben]

Only through VBA

You need a function to determine the font color then use that font color index
number as the criterion for the condition.

For more on color functions see Chip Pearson's site.

http://www.cpearson.com/excel/colors.htm


Gord Dibben MS Excel MVP

 

[Darren Bartrup]

Create a defined name (INSERT ~ NAME ~ DEFINE)

Call it something like CELLCOLOUR and in the Refers to: box enter:
=GET.CELL(24,INDIRECT(ADDRESS(0,0,4,FALSE),FALSE))
Which will return the font colour index of the current cell.

Can then be used in a formula:
=IF(CellColour=3,"Red","Something else")

More help on GET.CELL:
http://www.mrexcel.com/archive2/18800/21312.htm

http://www.jkp-ads.com/Articles/ExcelNames08.htm

 

[Angeles]

Thank you very much all of you.

Create a defined name (INSERT ~ NAME ~ DEFINE)

Call it something like CELLCOLOUR and in the Refers to: box enter:
=GET.CELL(24,INDIRECT(ADDRESS(0,0,4,FALSE),FALSE))
Which will return the font colour index of the current cell.

Can then be used in a formula:
=IF(CellColour=3,"Red","Something else")

More help on GET.CELL:
http://www.mrexcel.com/archive2/18800/21312.htm

http://www.jkp-ads.com/Articles/ExcelNames08.htm