Excel 2010 IF Statements/Conditional Formatting

[keef247]

Hi guys,
just a quick one as it's been awhile since I've used IF statements and conditional formatting...
basically I want to use the key as pictured here to highlight and fill the F column accordingly based upon the key's fields.

I want the colours to be filled as per the key also.

Cheers!

[DBarber]

not done much excel recently(long time ago) but logically looks like you are approaching this the wrong way,
the test should be whether or not there is a value in d2 and the resulting true value should be a ref to the formatting not the value held in the cell

[cookie365]

It's not very elegant, but you could try something along the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sheet1
        For Each rCell In .Range("F2:F500")
            If rCell.Value = "com" Then
                rCell.Interior.Color = RGB(69, 139, 0)
            ElseIf rCell.Value = "fin" Then
                rCell.Interior.Color = RGB(75, 0, 130)
            ElseIf rCell.Value = "sup" Then
                rCell.Interior.Color = RGB(255, 255, 0)
            ElseIf rCell.Value = "nfa" Then
                rCell.Interior.Color = RGB(0, 0, 0)
             ElseIf rCell.Value = "cust" Then
                rCell.Interior.Color = RGB(70, 130, 180)
            ElseIf rCell.Value = "3rd" Then
                rCell.Interior.Color = RGB(255, 165, 0)
            ElseIf rCell.Value = "og" Then
                rCell.Interior.Color = RGB(240, 128, 128)
            Else
                rCell.Interior.Color = RGB(255, 255, 255)
            End If
        Next rCell
    End With
End Sub

[keef247]

I did a similar thing years ago which was something like IF F2=X then FILL Colour Insert text X etc the above doesn't look familiar at all :\

[Galant]

Seems like something's missing in your explanation. If I'm reading it right you want the text and colour from a cell in Column H to appear in column F. However, unless I'm missing it, you haven't specified the triggers for that action.

So the question is, based on what do you want column F to be filled from Column H?

e.g. - Would I be right in assuming that you would want the cell in F to be filled with "com" (green) when the Cell in D (actual completion date) is filled?
Not sure how some of those would be triggered.

[keef247]

yes mate that's pretty much it to a t. if F = H's com then fill F with 'complete' etc