Conditional Formatting of Entire Row when the Active Cell is in ro

[Shannon]

I would like to highlight/shade the entire row that currently has the
active/selected cell. For example, whenever I am anywhere on row 10 I would
like all of row 10 to be highlighted or shaded a certain colour. I have been
looking for a function like ACTIVEROW or ACTIVECELL so that I can then use
conditional formatting, but can't find anything.

I am using EXCEL 2007.

Please help. Thanks.

 

[L. Howard Kittle]

Hi Shannon,

Try this from J.E

'J.E. McGimpsey 6/15/2001
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static oldRange As Range
Static colorIndices(256) As Integer
Dim i As Integer

If Not oldRange Is Nothing Then 'Restore color indices
For i = 1 To 256
Cells(oldRange.Row, i).Interior.ColorIndex = colorIndices(i)
Next i
End If
For i = 1 To UBound(colorIndices)
colorIndices(i) = Cells(ActiveCell.Row, i).Interior.ColorIndex
Next i
ActiveCell.EntireRow.Interior.ColorIndex = 15
Set oldRange = ActiveCell.EntireRow
End Sub

HTH
Regards,
Howard

 

[L. Howard Kittle]

Hi again Shannon,

Perhaps in the future you may want to highlight a row but contain it in a
certain range. Try this, which I believe I authored. Click on any cell in
range B8:K22 and that row will be highlighted From columns B to K.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row > 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column > 11 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35

End Sub

HTH
Regards,
Howard

 

[Shannon]

Thanks for the assist; but I found a relatively useful add-in that does what
I need. I was just hoping for an function that would do this easily.

Shannon

 

[robert morris]

L. Howard,

I'm just trying to learn and like your VBA. What would I change if I had a
Range of B8:G25 ?

Bob M.

 

[Shannon]

I used the coding from your first example and that works; however....

I also have a conditional format on all the rows to highlight every other
row. The coding for that is: =MOD(ROW(),2)

The problem is that the coding to highlight the active cell's row does NOT
overcome the conditional format highlight. It does work on the rows without
the conditionally formatted highlight.

Is there a way to make this "highlight the active cell's row" code highlight
the rows that also have the conditional formatted highlight?

 

[L. Howard Kittle]

Hi Robert,

It would look light this: New code below.

i - would remain the same because it refers to column B
j - is now 25 which is the last row

So the If statement below asks if the active cell is less than row 8 or
greater than row 25. If either is true then exit sub, it out of the range.

Then there is the activecell resize code.
k = the activecell column number, lets say it is column E which is 6.

From the activecell offset 0 rows and since you are going to the left you
use a minus - in front of the column offset. So... -(k - i) k = 6 - i =
2 would be minus four columns to the left which is B... that is good.

Now to Resize, we want 1 row and 6 columns to be highlighted. (If you
change the 1 to a 2 it will highlight the activecell row and the one below
it.)

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 6).Interior.ColorIndex = 35

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 25
k = ActiveCell.Column()
Set Data = Range("B8:G25")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 8 Or ActiveCell.Row > 25 Or _
ActiveCell.Column < 2 Or ActiveCell.Column > 7 Then
Exit Sub
End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 6).Interior.ColorIndex = 35

End Sub

HTH
Regards,
Howard

 

[Gord Dibben]

Try Chip Pearson's ROWLINER add-in. Fully customizable.

http://www.cpearson.com/excel/RowLiner.htm


Gord Dibben MS Excel MVP

 

[robert morris]

L. Howard,

Beautifully written explanation! As I said, I browse the discussion group
trying to learn from real questions asked by real people seeking real
solutions. I installed the Code and have now had fun changing numbers to see
what happens.

Thanks ever so much.

Bob

 

[L. Howard Kittle]

Thanks for the feed back and glad to help.

A hint:

The reason for Offset 'AND THEN' Resize is that Resize does not accept a
minus. So you have to go the cell of choice, by Offset which does accept
minus (left), and then indicate by resize, the cells you want to affect,
select, format...etc.

Fun
HTH
Regards
Howard

 

[Shannon]

The problem with the Rowliner (I've tried it) is that it doesn't work when
you protect the sheet (then again neither does the coding above).

Thanks though

 

[James]

I have the similar problem, I tried that rowliner from cpearson, but did not
like how it disabled the undo option. What was this add in that you found?
Sounds like something I am looking for.