How to test the text font color in a cell

[Yves]

Hello,
I would like to test if the text font in a cell is red or black. Is there a
way using any Excel function (i did not find any), or a VBA macro?
Thanks for any answer
Yves

 

[icestationzbra]

click on a cell that has text in either red or black colour and then ru
this macro. it would tell you the colour. i found out the colorindex b
recording a macro, sometimes the easiest and quickest way to solv
these kinds of problems.

Option Explicit

Sub TestColour()

If ActiveCell.Font.ColorIndex = 3 Then MsgBox "red"
If ActiveCell.Font.ColorIndex = 1 Then MsgBox "black"
If ActiveCell.Font.ColorIndex <> 1 Or ActiveCell.Font.ColorIndex <>
Then
MsgBox "add colorindex to vba code"
End If

End Su

 

[bigwheel]

Something like this should do it ...

If Range("A1").Font.ColorIndex = 3 Then
MsgBox "Its red"
End If

 

[Yves]

Hello,
Many thanks to both of you, i will copy the macro. Its a pity that Excel
don't offer a function to do that.
Anyway, many thanks again.
Yves

 

[David McRitchie]

If you want to go the extra step and keep adding for anything not found
you would be better off using CASE statement. Though I think it
would probably be a matter of it either being RED or not.
An Example of Case can be found in
http://www.mvps.org/dmcritchie/excel/event.htm#case

To just switch between the two colors the event macro is restricted
to Columns "B:E", and column G and no changes to Row 1.

Install with a right click on the worksheet tab, view code, ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
If Intersect(Range(Target(1).Address), _
Range("B:E", "G:G")) Is Nothing Then Exit Sub
If Target.Font.ColorIndex = 3 Then
Target.Font.ColorIndex = 1
Else
Target.Font.ColorIndex = 3
End If
End Sub