row and col highlight in range

_TJ_

New Member
Joined
Mar 16, 2008
Messages
47
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
try this code

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_

New Member
Joined
Mar 16, 2008
Messages
47
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

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
right click on the sheet name and select View Code from the list, on the new window paste this code:

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_

New Member
Joined
Mar 16, 2008
Messages
47

ADVERTISEMENT

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

_TJ_

New Member
Joined
Mar 16, 2008
Messages
47
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_

New Member
Joined
Mar 16, 2008
Messages
47
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,494
Messages
5,625,083
Members
416,070
Latest member
Austen G

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top