Identify a background colour and put x in cell

[Lensmeister]

Hi,

Hope this not a strange one to try and explain.

I have a very large spreadsheet with over 5000 lines of data. Some have a red background in the. between columns W and AL. in cell AT2 I want a formula that basically puts an X if the criteria is met.

i.e.

If any of the cells between W2:AL2 had a RED background then put a X in cell AT2.

Can anyone help please.

thanks

 

[VoG]

Try this User Defined Function which goes in a standard module

Function IsRed(r As Range) As String
Application.Volatile
Dim c As Range
For Each c In r
    If c.Interior.Color = vbRed Then
        IsRed = "X"
        Exit For
    End If
Next c
End Function

To use as a worksheet formula

=isred(W2:AL2)

If you change the colouring you will need to press F9 to recalculate.

 

[Lensmeister]

FANTASTIC .. This works BRILLIANTLY .. Thank to you much