formatting cells

[ockie]

Hi,
seeking help on my following problem
I am trying to color a cell eg A1. if there is a value in B1, C1 etc A1 will turn yellow. but as soon as there is no value in a cell eg D1 A1 reverts to normal. In summary A1 will be highlighted as long as the is a value in B1:Z1
thanks robert

 

[ockie]

Try this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrTrap

    Dim i As Long, j As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'If the entry just made is within columns N to AG (inclusive), then...
    If Target.Column >= 14 And Target.Column <= 33 Then
        '...check each row and if there are no blanks from Col. N up and including the column where the entry was just made (up to Col. AG), then...
        j = Range("N:AG").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For i = 1 To j
            If Evaluate("COUNTA(N" & i & ":" & Split(Target.Address, "$")(1) & i & ")") = (Target.Column - 14) + 1 Then
                '...colour Col. D of the row just updated yellow
                Range("D" & i).Interior.Color = vbYellow
            'Else...
            Else
                '...remove the fill colour from Col. D of the row just updated as there's a gap of data being entered sequentially
                Range("D" & i).Interior.Color = xlNone
            End If
        Next i
    End If
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
  
Exit Sub

ErrTrap:

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Thanks Trebor, it works just as I had envisioned, I just needed someone with a lot more knowledge of VBA to assist, again thank you very much for your assistance it is very much appreciated.

 

[Trebor76]

thank you very much for your assistance it is very much appreciated.

Thanks for letting us know and you're welcome