row and col highlight in range

[_TJ_]

Hi all

How would I go about being able to colur both the column and row in a block of cells B2:J10 it the number matches what's in A1, there will be multiple numbers so I'd need all relevant cols and rows to be coloured, but if A1 is clear then no colour is needed.

Cheers

 

[iggydarsa]

try this code

Sub ColorMe()
Range("B:J").Interior.ColorIndex = xlNone
Range("2:10").Interior.ColorIndex = xlNone
For Each cl In Range("B2:J10")
    If cl.Value = Range("A1").Value Then
        cl.EntireRow.Interior.Color = vbYellow
        cl.EntireColumn.Interior.Color = vbYellow
    End If
Next cl
End Sub

 

[_TJ_]

Thank you, it does highlight the whole col and row though rather than just inside the range required.

Also, is there a way to make it auto run when data is entered in A1?

 

[iggydarsa]

right click on the sheet name and select View Code from the list, on the new window paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
    Range("B2:J10").Interior.ColorIndex = xlNone
    For Each cl In Range("B2:J10")
        If cl.Value = Range("A1").Value Then
            Range("B" & cl.Row & ":J" & cl.Row).Interior.Color = vbYellow
            Range(Cells(2, cl.Column), Cells(10, cl.Column)).Interior.Color = vbYellow
        End If
    Next cl
End If
End Sub

 

[_TJ_]

Absolute brilliance, thank you, it works like a charm.

 

[_TJ_]

Ah, almost. Once a number has been entered into A1 then removed the whole range turns yellow rather than back to no fill.

Any thoughts?

Thanks again.

 

[_TJ_]

Sorry, can't find an edit feature.

The formula works on the condition I acutally enter the information into the cells but sometimes the numbers are entered via a macro that copies and pastes values from another sheet which doesn't get picked up by the current piece of code.

Is there a way around this?

 

[_TJ_]

Any other thoughts to work around the above?

Cheers.