Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is in ro
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is in ro
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 "Shannon" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is in ro
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
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. "L. Howard Kittle" wrote: 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
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 "robert morris" wrote in message ... 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. "L. Howard Kittle" wrote: 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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
Try Chip Pearson's ROWLINER add-in. Fully customizable.
http://www.cpearson.com/excel/RowLiner.htm Gord Dibben MS Excel MVP On Wed, 5 Mar 2008 09:22:00 -0800, Shannon wrote: 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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
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" wrote: 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 "robert morris" wrote in message ... 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. "L. Howard Kittle" wrote: 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" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
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 "robert morris" wrote in message ... 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" wrote: 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 "robert morris" wrote in message ... 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. "L. Howard Kittle" wrote: 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" wrote in message ... 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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting of Entire Row when the Active Cell is i
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. "Shannon" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use conditional formatting for an entire column? | Excel Discussion (Misc queries) | |||
conditional formatting for entire row | Excel Discussion (Misc queries) | |||
Conditional Formatting Entire Row | Excel Discussion (Misc queries) | |||
Highlight the entire row or particular cell using conditional formatting | New Users to Excel | |||
Conditional formatting of an entire row based on a cell text entry | Excel Discussion (Misc queries) |