Test for Color

[sachavez]

Excel 2003

Need code to look at range F10:F21 and determine if any cells in the range are yellow. If "true," need cell F2 to turn yellow and include message saying "True."

Can you help?

Thanks

 

[VoG]

Try

Sub test()
Dim c As Range
For Each c In Range("F10:f21")
    If c.Interior.ColorIndex = 6 Then
        With Range("F2")
            .Value = True
            .Interior.ColorIndex = 6
        End With
        Exit For
    End If
Next c
End Sub

 

[sachavez]

Thanks, Peter.

Could you update for a worksheet change? I'd like to put the code in the Sheet1 tab.

Steve

 

[VoG]

That won't work if the color is from Conditional Formatting and changing a fill color won't trigger a worksheet change event.

 

[sachavez]

Can we have the code look for specfic text, say "color me yellow"?

 

[VoG]

What inserts that text - you or a formula?

 

[sachavez]

The user selects the text from a drop down box.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F10:f21")) Is Nothing Then
    If Target.Value = "color me yellow" Then
        With Range("F2")
            .Value = True
            .Interior.ColorIndex = 6
        End With
    End If
End If
End Sub

 

[sachavez]

Looking good. I modified the code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F10:F21")) Is Nothing Then
    If Target.Value = "Repair needed - non Roadable" Then
        With Range("F2")
            .Value = True
            .Interior.ColorIndex = 6
            .Value2 = "Flat Bed Truck Required"
        End With
    End If
End If
End Sub

When range F10:F21 cleared, F2 remains yellow with the text "Flat Bed Truck Required". How can we get F2 to be blank if range F10:F21 does not meet the criteria?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F10:F21")) Is Nothing Then
    Range("F2").Clear
    If Target.Value = "Repair needed - non Roadable" Then
        With Range("F2")
            .Value = True
            .Interior.ColorIndex = 6
            .Value2 = "Flat Bed Truck Required"
        End With
    End If
End If
End Sub

Why Value2 and not Value?