Auto colour a range of of cells in active row

[Sparky]

I have data in cells B14:O14 down to B33:O33. Could anyone help out by providing the code required to auto colour any row range from above when any cell from B14 to O14 is selected.

I am using 2003.

Thanks in advance

 

[Sektor]

Insert into sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B14:O14")) Is Nothing Then
        Range("B1:O13").Interior.ColorIndex = 16
    Else
        Range("B1:O13").Interior.ColorIndex = xlNone
    End If
End Sub

 

[VoG]

Select B14:O33 and apply this Conditional Formatting formula

=ROW()=CELL("row")

and apply a fill colour. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

 

[Sparky]

Thanks to both of you for your quick replies however, Sektor your code does not work, maybe its something to do with my range of data (B14:O33).

VoG

Your CF and code works perfectly. Thank you.

 

[Sektor]

You said "above". Above what? I assumed that it's out of your data range.

 

[Sparky]

Sektor

I have just re-read my question and it is misleading. I apologise for that. What I was referring to was my actual data range (B14:O33) and not everything above B14:O14.

Could you please provide your solution as a matter of interest.

Once again Thank You

 

[Sektor]

In B14:O33 it highlights all rows from B14:O14 till current row. Correct?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B14:O33")) Is Nothing Then
        Range("B14:O" & Target.Row).Interior.ColorIndex = 6
    Else
        Range("B1:O33").Interior.ColorIndex = xlNone
    End If
End Sub

 

[Sparky]

Sektor

From my data range (B14:O33) I need to be able to select any cell from B14 to B33 and if for example I select B20 I would like B20:O20 only to be highlighted, not the data above or below. When I select another cell from B14 to B33 I would expect only the new selection to be highlighted.

Hope this helps

Thanks

 

[Sektor]

Then this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B14:B33")) Is Nothing Then
        Range("B1:O33").Interior.ColorIndex = xlNone 'Remove prev coloring
        Range("B" & Target.Row & ":O" & Target.Row).Interior.ColorIndex = 6
    Else
        Range("B1:O33").Interior.ColorIndex = xlNone
    End If
End Sub

 

[Sparky]

Sektor

Perfect. Thanks for your persistance. It is the easier option.

Thanks to both Sektor & VoG.