Formula for conditional formating to colour 15 cells if value = a in one?

[tonywatsonhelp]

Hi Everyone,
Ok so I'm looking for away to set up condional formatting or a vba to do the following

Range AL51:AL800

if cell value = "a" then cell and the 14 cells above true, or colour fill white.

so basically, for every cell in the range if there's an "a" that cell and the 14 above it are filled white?

please help if you can thanks

Tony

 

[Fluff]

How about
=COUNTIF(AL51:AL65,"a")

 

[Snakehips]

Tony,
With range AL51:AL800 selected enter the following custom formula.

=COUNTIF(AI51:AI65,"a")>=1

I think that should do it provided you do not get any stray a's in Al below 800
You will need to tweak if you want cells above row51 to be white based on AL51 =a

Hope that helps.

 

[mumps]

Try:

Sub fillRange()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("AL51:AL800")
        If rng = "a" Then
            Cells(rng.Row - 14, "AL").Resize(15).Interior.ColorIndex = xlNone
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Thanks Snakehips, seams to be working,
mumps, really handy to have this as it often more stable doing it that way so thank you.
Tony