conditional formatting by code?

[fry]

Hi All

I've just played around with conditional formatting trying to make a row of cells (B4 to K4) turn red when one of the cells (J4) has an entry but I'm failing fast.

Can this be done automatically by code??

Thanks

 

[Lewiy]

You can do it with this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("J4").Value <> "" Then
    Range("B4:K4").Interior.ColorIndex = 3
    Else
        Range("B4:K4").Interior.ColorIndex = xlColorIndexNone
End If
End Sub

But for conditional formatting:
Highlight the range B4:K4, select Conditional Formatting:
Formula Is:

=$J$4<>""

 

[fry]

Hi Lewiy

Oh dear, slight problem Although the above does work (thank you for that) it doesn't do just that row, it does everything.........my fault, I didn't think this one through.

I've just realised that its only J4 on the first line (DOH!!).......then J5 for the next line, J6 for the next etc............I think my mind has gone at last!?!?

Oops springs to mind

 

[Lewiy]

Ok, if I understand correctly then try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 And Target.Row >= 4 Then
    If Target.Value <> "" Then
        Range("B" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = 3
        Else
            Range("B" & Target.Row & ":K" & Target.Row).Interior.ColorIndex = xlColorIndexNone
    End If
End If
End Sub

I have assumend that you don't want it to act on rows 1 to 3.

 

[fry]

Hi Lewiy

I think I've sorted it. My brain got into gear for 5 mins.

What I did was to change your =$J$4<>"" to =$J4<>"" and enter this in conditional formatting on the first row (B4:K4).

Then I highlighted the same row and copied, after which I highlighted the row below (B5:K5) and dragged down to B500:K500. I then did a paste special and pasted the format only..........Hey Presto it seemed to work!!

I wouldn't have been able to do that without your suggestion though so I thank you very much for your time and patience with me.