A
Angeles
Is is possible to define a condition depending of the text color of cells ?
Thank you
Thank you
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
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---
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---
G
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
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
D
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
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
A
Angeles
Thank you very much all of you.
Darren Bartrup said: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