Display a red X if Range contains the same

[Donal28]

Hi All

I'm looking for a formula that will place a red x in a cell if a certain range contains a red x. The range is L8:L15 and the cell I would like to display the red X in is AD6.

Any help on this would be very much appreciated

Regards

Donal

 

[Gerald Higgins]

Could there be any other entries in L8:L15 ?

 

[jim may]

Maybe somthing like this?

Sub foo()
Set Rng = ActiveSheet.Range("L8:L15")
    With Range("AD6")
        .Value = ""
        .Font.ColorIndex = xlAutomatic
    End With
For Each c In Rng
    If c.Value = "x" Then
        If c.Font.Color = vbRed Then
            With Range("AD6")
                .Value = "x"
                .Font.ColorIndex = 3
            End With
        End If
    End If
Next c
End Sub

 

[Donal28]

Could there be any other entries in L8:L15 ?

Thanks for the reply Gerald, yes there are other entries in this range, green Os

 

[Donal28]

Maybe somthing like this?

Sub foo()
Set Rng = ActiveSheet.Range("L8:L15")
    With Range("AD6")
        .Value = ""
        .Font.ColorIndex = xlAutomatic
    End With
For Each c In Rng
    If c.Value = "x" Then
        If c.Font.Color = vbRed Then
            With Range("AD6")
                .Value = "x"
                .Font.ColorIndex = 3
            End With
        End If
    End If
Next c
End Sub

Thanks for the reply Jim, I have tried this code but nothing happens, maybe I'm not implementing the macro properly.....I hit ALT and F11 and entered the code but nothing happens when a red x is entred in the range L8:L15. Do I need to attach the code toa button or something?

 

[jim may]

Did you paste the code into a Standard Module (say like Module1)?

Currently, you need to run "Foo" AFTER you make any changes to your range(L8:L15).
So make a change in L8:L15 then Run Foo.

A Forms-type button could also be placed on your worksheet to run Foo.

 

[mikerickson]

If the red X in L8:L15 got that way via Conditional Formatting, it would be easier to detect the condition that caused the redness than it is to test if the cell is red.

 

[Donal28]

Did you paste the code into a Standard Module (say like Module1)?

Currently, you need to run "Foo" AFTER you make any changes to your range(L8:L15).
So make a change in L8:L15 then Run Foo.

A Forms-type button could also be placed on your worksheet to run Foo.

Yess thats worked alright...thanks so much for your help

 

[jim may]

Mikerickson is right... I presently HAVE NOT considered that you have activated any conditional formatting...

 

[Donal28]

If the red X in L8:L15 got that way via Conditional Formatting, it would be easier to detect the condition that caused the redness than it is to test if the cell is red.

Thanks for the reply but the red Xs aren't a result of conditioning formatting