Determining Target font colour

[kirkm]

I'm wanting to detect if the font colour in the selected cell is black
or yellow.
Been playing with target. and the drop down items but can't see
anything (yet).

Any help appreciated.
Thanks - Kirk

 

[JLGWhiz]

If the cell interior is filled by standard format method, then:

Private Sub Worksheet_Change(ByVal Target As Range)
Set myRange = Sheets(1).Range("A1")
If Target = myRange Then
MsgBox myRange.Interior.ColorIndex
End If
End Sub

This next one is a little tricky. It basically does the same thing
but it only returns the setting for the conditional format and
does not tell you if the condition is met. That means that if
the condition is not met, the cell will not be color coded.

Private Sub Worksheet_Change(ByVal Target As Range)
Set myRange = Sheets(1).Range("A1")
If Target = myRange Then
MsgBox myRange.FormatConditions(1).Interior.ColorIndex
End If
End Sub

 

[JLGWhiz]

P.S. The code goes behind the worksheet. Right click the sheet tab and click
View Code in the drop down menu. Then paste it in.

 

[RyanH]

You can do this a few ways depending on what you prefer.

1.) Put this code in Worksheet Module or the worksheet you wish to test. I
think this may get a bit annoying but it will do the trick.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Font.ColorIndex
Case Is = 1
MsgBox "The Font ColorIndex = 1 or Black"
Case Is = 6
MsgBox "The Font ColorIndex = 6 or Yellow"
End Select

End Sub

2.) You can call a user defined function from a cell, which will return
Black or Yellow.

Public Function DetectFontColor(CellToDetect As Range) As String

Select Case CellToDetect.Font.ColorIndex
Case Is = 1
DetectFontColor = "Black"
Case Is = 6
DetectFontColor = "Yellow"
End Select

End Function

3.) You could use the immediate window in the VB Editor. Just select a cell,
then in VBE select View>>Immediate Window then type this:

?ActiveCell.Font.ColorIndex

This will give you the colorindex number.

I hope one of these helped! If so, please let me know and click "YES" below.

 

[JLGWhiz]

Also, see this site:

http://www.ozgrid.com/VBA/Functions.htm

about four or five bullets down the free download list.