Change the value of a cell according to the font colour of another cell

Question

I would like to change the value of a cell depending on the font color of another cell.

In three adjacent columns I have three different values. On one of the cells I have a value with green font.

I would like to make a condition that depending on which cell contains text/values with green font I can select the column where this text is located:

I tried to create a function to find the color of the font then use this function in an if statement.

Function IsColor(lColor As Long, cl As Range) As Boolean
If cl.Font.ColorIndex = lColor Then
    IsColor = True
Else
    IsColor = False
End If
End Function

Answer 1

Using your function the following might solve your issue:

The find method is illustrated here: VBA format cell based on fill color and font color

Option Explicit

Function IsColor(lColor As Long, cl As Range) As Boolean
If cl.Font.Color = lColor Then
    IsColor = True
Else
    IsColor = False
End If
End Function

Function findGreen(myRange As Range) As String
    Dim myCell As Range
    Dim tmpStr As String
    tmpStr = "no green color found!"
    For Each myCell In myRange
        'Debug.Print myCell.Address; myCell.Font.Color
        If IsColor(vbGreen, myCell) Then
            'Address or column ?!
            'tmpStr = myCell.Address
            tmpStr = "green in column " & Chr(64 + myCell.Column)
            Exit For
        End If
    Next myCell
    findGreen = tmpStr
End Function

Sub test_findGreen()
    Call findGreen(Range("A2:C2"))
End Sub