Hello I am working in Excel 2007, on a long Table from $A$1:$DO$9 so far
which will be growing vertically with each entry, and maybe in some special
cases new columns will be added but not often at all. Data is located from
E6:DO9, the rest of the cells in the Table are just Headers and so on. So
what I would like to have is the following:

A)
When the active cell is inside E6:D09, for example:
G6, to have $A6:$D6 highlighted in red lets say, and G$5 in red as well...
if I move to cell G7, I would have $A7:$D7 and G$5 in red, and if I move to
H7, I would have$A7:$D7 and H$5 higlighted.

B)
Aside from that I guess this part is a bit more complicated, I hope it is
possible as well... Each row is a entry, so on a row I will have several
cells with data (a number, the rest are empty) so on the current row to
highlight the titles (row 5) which have data in the current row, in orange.

So in summary, the idea is to highlight the titles (Row 5) that have data on
the active row in orange and at the same time to highlight the current title
(Row 5) and A:D of the current row in Red... I really hope I was able to
explain myself...

Thank you very much for your attention!

A working copy of this that you can upload and use is he
http://www.jlathamsite.com/uploads/A...ight_forEd.xls

All of this code goes into the specific sheet's code module. No doubt that
the editor here is going to break some lines so might be better to cut and
paste from the sample into your workbook. I've tried to anticipate the
breaks, but ... Instructions for getting into a worksheet's code module can
be found he http://www.jlathamsite.com/Teach/WorksheetCode.htm

The sheet name is not important, and the code automatically adjusts for any
added rows of data, and as long as your titles in row 5 extend on out without
a break (empty cell), the highlighting of that row will work properly.

Option Explicit
Dim LastHighlightedRow As Long
Dim LastHighlightedCol As Long
Const FirstDataRow = 6
Const FirstDataCol = 5
Const LastPossibleRow = 65536 ' change for Excel 2007
Const Red = 3
Const Orange = 46

Private Sub Worksheet_Activate()
Dim LastDataColumn As Long ' for Excel 2007
Dim ColumnToExamine As Long ' again for Excel 2007
Dim DataRange As String
Dim anyCell As Object
Application.EnableEvents = False
'set up Orange highlighting of used columns
'could take some time, depending on speed of system
LastDataColumn = Range("A" & _
FirstDataRow - 1).End(xlToRight).Column
For ColumnToExamine = FirstDataCol To LastDataColumn
SetColumnTitleShading ColumnToExamine - 1 ' using as an Offset
Next
'initialize values if possible
If ActiveCell.Row = FirstDataRow Then
LastHighlightedRow = ActiveCell.Row
End If
If ActiveCell.Column = FirstDataCol Then
LastHighlightedCol = ActiveCell.Column
End If
'set red highlight in column title if needed
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(FirstDataRow - 2, _
LastHighlightedCol - 1).Interior.ColorIndex = Red
Else
'find last selected cell in data range
'THIS! could take a while for large data area!!
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":" & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim currentRow As Long
Dim currentCol As Long
Dim DataRange As String
Dim anyCell As Object

If Target.Row < FirstDataRow Or Target.Column < FirstDataCol Then
Exit Sub ' not in data area
End If
Application.EnableEvents = False
currentRow = Target.Row
currentCol = Target.Column
'remove highlight from previously selected cell
'if last selected cell is outside of the data range then
'LastHighlightedRow and LastHighlightedCol will be zero
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(LastHighlightedRow - 1, _
LastHighlightedCol - 1).Interior.ColorIndex = xlNone
Else
'find a red cell in data area?
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":"
DataRange = DataRange & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
ActiveCell.Interior.ColorIndex = Red ' highlight selected cell
'changed columns? as moving across sheet?
If currentCol < LastHighlightedCol Then
If LastHighlightedCol 0 Then
'reset previous Row 5 title shading
SetColumnTitleShading LastHighlightedCol - 1
End If
Range("A1").Offset(FirstDataRow - 2, _
currentCol - 1).Interior.ColorIndex = Red
LastHighlightedCol = currentCol
End If
'changed rows? as moving up/down sheet?
If currentRow < LastHighlightedRow Then
If LastHighlightedRow 0 Then
Rows(LastHighlightedRow).Interior.ColorIndex = xlNone
End If
Range("A" & currentRow & ":D" & _
currentRow).Interior.ColorIndex = Red
LastHighlightedRow = currentRow
End If
Application.EnableEvents = True

End Sub

Private Sub SetColumnTitleShading(WhichColumn As Long)
If Range("A1").Offset(LastPossibleRow - 1, _
WhichColumn).End(xlUp).Row < FirstDataRow Then
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = xlNone
Else
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = Orange
End If
End Sub


"Ed" wrote:

Hello I am working in Excel 2007, on a long Table from $A$1:$DO$9 so far
which will be growing vertically with each entry, and maybe in some special
cases new columns will be added but not often at all. Data is located from
E6:DO9, the rest of the cells in the Table are just Headers and so on. So
what I would like to have is the following:

A)
When the active cell is inside E6:D09, for example:
G6, to have $A6:$D6 highlighted in red lets say, and G$5 in red as well...
if I move to cell G7, I would have $A7:$D7 and G$5 in red, and if I move to
H7, I would have$A7:$D7 and H$5 higlighted.

B)
Aside from that I guess this part is a bit more complicated, I hope it is
possible as well... Each row is a entry, so on a row I will have several
cells with data (a number, the rest are empty) so on the current row to
highlight the titles (row 5) which have data in the current row, in orange.

So in summary, the idea is to highlight the titles (Row 5) that have data on
the active row in orange and at the same time to highlight the current title
(Row 5) and A:D of the current row in Red... I really hope I was able to
explain myself...

Thank you very much for your attention!

Hello and thanks a lot for this very long impressive code! It happy with it
and it works perfectly for my purpose, but I would like to know if it is
possible to make a change with the "Orange" highlighted cells. In this
particular file that you sent me, it would be great if the orange "titles"
would be highligted just for the current row rather than the entire column.
For example, if my active cell is in Row 6, there are none orange titles, if
I move to Row 7, title N$5 is highlighted orange, then Row 8 nothing again,
and Row 9 G$5 and so on... If I would have severl entries in a row then those
corresponding titles would be highlighted, I don't really know but it is
hard to program or if it will take lots of time with all that checking it has
to do... thanks again!


"JLatham" wrote:

A working copy of this that you can upload and use is he
http://www.jlathamsite.com/uploads/A...ight_forEd.xls

All of this code goes into the specific sheet's code module. No doubt that
the editor here is going to break some lines so might be better to cut and
paste from the sample into your workbook. I've tried to anticipate the
breaks, but ... Instructions for getting into a worksheet's code module can
be found he http://www.jlathamsite.com/Teach/WorksheetCode.htm

The sheet name is not important, and the code automatically adjusts for any
added rows of data, and as long as your titles in row 5 extend on out without
a break (empty cell), the highlighting of that row will work properly.

Option Explicit
Dim LastHighlightedRow As Long
Dim LastHighlightedCol As Long
Const FirstDataRow = 6
Const FirstDataCol = 5
Const LastPossibleRow = 65536 ' change for Excel 2007
Const Red = 3
Const Orange = 46

Private Sub Worksheet_Activate()
Dim LastDataColumn As Long ' for Excel 2007
Dim ColumnToExamine As Long ' again for Excel 2007
Dim DataRange As String
Dim anyCell As Object
Application.EnableEvents = False
'set up Orange highlighting of used columns
'could take some time, depending on speed of system
LastDataColumn = Range("A" & _
FirstDataRow - 1).End(xlToRight).Column
For ColumnToExamine = FirstDataCol To LastDataColumn
SetColumnTitleShading ColumnToExamine - 1 ' using as an Offset
Next
'initialize values if possible
If ActiveCell.Row = FirstDataRow Then
LastHighlightedRow = ActiveCell.Row
End If
If ActiveCell.Column = FirstDataCol Then
LastHighlightedCol = ActiveCell.Column
End If
'set red highlight in column title if needed
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(FirstDataRow - 2, _
LastHighlightedCol - 1).Interior.ColorIndex = Red
Else
'find last selected cell in data range
'THIS! could take a while for large data area!!
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":" & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim currentRow As Long
Dim currentCol As Long
Dim DataRange As String
Dim anyCell As Object

If Target.Row < FirstDataRow Or Target.Column < FirstDataCol Then
Exit Sub ' not in data area
End If
Application.EnableEvents = False
currentRow = Target.Row
currentCol = Target.Column
'remove highlight from previously selected cell
'if last selected cell is outside of the data range then
'LastHighlightedRow and LastHighlightedCol will be zero
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(LastHighlightedRow - 1, _
LastHighlightedCol - 1).Interior.ColorIndex = xlNone
Else
'find a red cell in data area?
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":"
DataRange = DataRange & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
ActiveCell.Interior.ColorIndex = Red ' highlight selected cell
'changed columns? as moving across sheet?
If currentCol < LastHighlightedCol Then
If LastHighlightedCol 0 Then
'reset previous Row 5 title shading
SetColumnTitleShading LastHighlightedCol - 1
End If
Range("A1").Offset(FirstDataRow - 2, _
currentCol - 1).Interior.ColorIndex = Red
LastHighlightedCol = currentCol
End If
'changed rows? as moving up/down sheet?
If currentRow < LastHighlightedRow Then
If LastHighlightedRow 0 Then
Rows(LastHighlightedRow).Interior.ColorIndex = xlNone
End If
Range("A" & currentRow & ":D" & _
currentRow).Interior.ColorIndex = Red
LastHighlightedRow = currentRow
End If
Application.EnableEvents = True

End Sub

Private Sub SetColumnTitleShading(WhichColumn As Long)
If Range("A1").Offset(LastPossibleRow - 1, _
WhichColumn).End(xlUp).Row < FirstDataRow Then
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = xlNone
Else
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = Orange
End If
End Sub


"Ed" wrote:

Hello I am working in Excel 2007, on a long Table from $A$1:$DO$9 so far
which will be growing vertically with each entry, and maybe in some special
cases new columns will be added but not often at all. Data is located from
E6:DO9, the rest of the cells in the Table are just Headers and so on. So
what I would like to have is the following:

A)
When the active cell is inside E6:D09, for example:
G6, to have $A6:$D6 highlighted in red lets say, and G$5 in red as well...
if I move to cell G7, I would have $A7:$D7 and G$5 in red, and if I move to
H7, I would have$A7:$D7 and H$5 higlighted.

B)
Aside from that I guess this part is a bit more complicated, I hope it is
possible as well... Each row is a entry, so on a row I will have several
cells with data (a number, the rest are empty) so on the current row to
highlight the titles (row 5) which have data in the current row, in orange.

So in summary, the idea is to highlight the titles (Row 5) that have data on
the active row in orange and at the same time to highlight the current title
(Row 5) and A:D of the current row in Red... I really hope I was able to
explain myself...

Thank you very much for your attention!

Ed,
Either I'm not understanding things here, or it's not quite working right
where you're using it.
What SHOULD happen is that initially any columns from E out to DO (and
beyond) that have any entry in the column below them should be highlighted
orange.

When you select a cell in within the data area (E6:DO9 from example, but DO
and 9 are variable and should automatically adjust) then cells A:D on that
row and the cell you selected should turn Red along with the cell in row 5 of
that column. When you move off to another column in the same row, old cell
should turn plain white, new selected cell should turn red, old cell in Row 5
should either turn unshaded or back to orange depending on whether or not
there's data in that old column, and new column, Row 5 cell should turn red.

Examples:
You choose Cell H8:
A8:D8 and H5 go to red along with H8 itself

You then select I8 (moving over 1 column)
A8:D8 remain red, H8 clears, and I8 and I5 turn red, and
H5 either goes clear (no data below it in column H), or
H5 goes to orange (some data in a cell in Column H below H5.

If you move down the column from H8, to say H9 then
A8:D8 go clear, A9:D9 turn Red along with H9, no change in H5 (still red)

Question #1: Is it not working that way?
Question #2: could you describe your desired operation kind of like how I
did just now, giving examples of what should happen to which cells as you
move around?


"Ed" wrote:

Hello and thanks a lot for this very long impressive code! It happy with it
and it works perfectly for my purpose, but I would like to know if it is
possible to make a change with the "Orange" highlighted cells. In this
particular file that you sent me, it would be great if the orange "titles"
would be highligted just for the current row rather than the entire column.
For example, if my active cell is in Row 6, there are none orange titles, if
I move to Row 7, title N$5 is highlighted orange, then Row 8 nothing again,
and Row 9 G$5 and so on... If I would have severl entries in a row then those
corresponding titles would be highlighted, I don't really know but it is
hard to program or if it will take lots of time with all that checking it has
to do... thanks again!


"JLatham" wrote:

A working copy of this that you can upload and use is he
http://www.jlathamsite.com/uploads/A...ight_forEd.xls

All of this code goes into the specific sheet's code module. No doubt that
the editor here is going to break some lines so might be better to cut and
paste from the sample into your workbook. I've tried to anticipate the
breaks, but ... Instructions for getting into a worksheet's code module can
be found he http://www.jlathamsite.com/Teach/WorksheetCode.htm

The sheet name is not important, and the code automatically adjusts for any
added rows of data, and as long as your titles in row 5 extend on out without
a break (empty cell), the highlighting of that row will work properly.

Option Explicit
Dim LastHighlightedRow As Long
Dim LastHighlightedCol As Long
Const FirstDataRow = 6
Const FirstDataCol = 5
Const LastPossibleRow = 65536 ' change for Excel 2007
Const Red = 3
Const Orange = 46

Private Sub Worksheet_Activate()
Dim LastDataColumn As Long ' for Excel 2007
Dim ColumnToExamine As Long ' again for Excel 2007
Dim DataRange As String
Dim anyCell As Object
Application.EnableEvents = False
'set up Orange highlighting of used columns
'could take some time, depending on speed of system
LastDataColumn = Range("A" & _
FirstDataRow - 1).End(xlToRight).Column
For ColumnToExamine = FirstDataCol To LastDataColumn
SetColumnTitleShading ColumnToExamine - 1 ' using as an Offset
Next
'initialize values if possible
If ActiveCell.Row = FirstDataRow Then
LastHighlightedRow = ActiveCell.Row
End If
If ActiveCell.Column = FirstDataCol Then
LastHighlightedCol = ActiveCell.Column
End If
'set red highlight in column title if needed
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(FirstDataRow - 2, _
LastHighlightedCol - 1).Interior.ColorIndex = Red
Else
'find last selected cell in data range
'THIS! could take a while for large data area!!
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":" & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim currentRow As Long
Dim currentCol As Long
Dim DataRange As String
Dim anyCell As Object

If Target.Row < FirstDataRow Or Target.Column < FirstDataCol Then
Exit Sub ' not in data area
End If
Application.EnableEvents = False
currentRow = Target.Row
currentCol = Target.Column
'remove highlight from previously selected cell
'if last selected cell is outside of the data range then
'LastHighlightedRow and LastHighlightedCol will be zero
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(LastHighlightedRow - 1, _
LastHighlightedCol - 1).Interior.ColorIndex = xlNone
Else
'find a red cell in data area?
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":"
DataRange = DataRange & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
ActiveCell.Interior.ColorIndex = Red ' highlight selected cell
'changed columns? as moving across sheet?
If currentCol < LastHighlightedCol Then
If LastHighlightedCol 0 Then
'reset previous Row 5 title shading
SetColumnTitleShading LastHighlightedCol - 1
End If
Range("A1").Offset(FirstDataRow - 2, _
currentCol - 1).Interior.ColorIndex = Red
LastHighlightedCol = currentCol
End If
'changed rows? as moving up/down sheet?
If currentRow < LastHighlightedRow Then
If LastHighlightedRow 0 Then
Rows(LastHighlightedRow).Interior.ColorIndex = xlNone
End If
Range("A" & currentRow & ":D" & _
currentRow).Interior.ColorIndex = Red
LastHighlightedRow = currentRow
End If
Application.EnableEvents = True

End Sub

Private Sub SetColumnTitleShading(WhichColumn As Long)
If Range("A1").Offset(LastPossibleRow - 1, _
WhichColumn).End(xlUp).Row < FirstDataRow Then
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = xlNone
Else
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = Orange
End If
End Sub


"Ed" wrote:

Hello I am working in Excel 2007, on a long Table from $A$1:$DO$9 so far
which will be growing vertically with each entry, and maybe in some special
cases new columns will be added but not often at all. Data is located from
E6:DO9, the rest of the cells in the Table are just Headers and so on. So
what I would like to have is the following:

A)
When the active cell is inside E6:D09, for example:
G6, to have $A6:$D6 highlighted in red lets say, and G$5 in red as well...
if I move to cell G7, I would have $A7:$D7 and G$5 in red, and if I move to
H7, I would have$A7:$D7 and H$5 higlighted.

B)
Aside from that I guess this part is a bit more complicated, I hope it is
possible as well... Each row is a entry, so on a row I will have several
cells with data (a number, the rest are empty) so on the current row to
highlight the titles (row 5) which have data in the current row, in orange.

So in summary, the idea is to highlight the titles (Row 5) that have data on
the active row in orange and at the same time to highlight the current title
(Row 5) and A:D of the current row in Red... I really hope I was able to
explain myself...

Thank you very much for your attention!

Hang on, I think I get a glimmer... See if this sounds like I understand:

You pick cell in Row 6, then cells in row 5 that correspond to cells in row
6 with data in them would turn orange, all others clear (except current
column which would be red).
Then you pick a cell in row 19, then cells in row 5 that correspond to cells
in row 19 with data in them would turn orange, all others clear, again except
current column which would be red in row 5?

If that's not close, then I think a little brief layout of possible data
with description of what should happen as you pick a series of cells within
the table is going to be needed.

"Ed" wrote:

Hello and thanks a lot for this very long impressive code! It happy with it
and it works perfectly for my purpose, but I would like to know if it is
possible to make a change with the "Orange" highlighted cells. In this
particular file that you sent me, it would be great if the orange "titles"
would be highligted just for the current row rather than the entire column.
For example, if my active cell is in Row 6, there are none orange titles, if
I move to Row 7, title N$5 is highlighted orange, then Row 8 nothing again,
and Row 9 G$5 and so on... If I would have severl entries in a row then those
corresponding titles would be highlighted, I don't really know but it is
hard to program or if it will take lots of time with all that checking it has
to do... thanks again!


"JLatham" wrote:

A working copy of this that you can upload and use is he
http://www.jlathamsite.com/uploads/A...ight_forEd.xls

All of this code goes into the specific sheet's code module. No doubt that
the editor here is going to break some lines so might be better to cut and
paste from the sample into your workbook. I've tried to anticipate the
breaks, but ... Instructions for getting into a worksheet's code module can
be found he http://www.jlathamsite.com/Teach/WorksheetCode.htm

The sheet name is not important, and the code automatically adjusts for any
added rows of data, and as long as your titles in row 5 extend on out without
a break (empty cell), the highlighting of that row will work properly.

Option Explicit
Dim LastHighlightedRow As Long
Dim LastHighlightedCol As Long
Const FirstDataRow = 6
Const FirstDataCol = 5
Const LastPossibleRow = 65536 ' change for Excel 2007
Const Red = 3
Const Orange = 46

Private Sub Worksheet_Activate()
Dim LastDataColumn As Long ' for Excel 2007
Dim ColumnToExamine As Long ' again for Excel 2007
Dim DataRange As String
Dim anyCell As Object
Application.EnableEvents = False
'set up Orange highlighting of used columns
'could take some time, depending on speed of system
LastDataColumn = Range("A" & _
FirstDataRow - 1).End(xlToRight).Column
For ColumnToExamine = FirstDataCol To LastDataColumn
SetColumnTitleShading ColumnToExamine - 1 ' using as an Offset
Next
'initialize values if possible
If ActiveCell.Row = FirstDataRow Then
LastHighlightedRow = ActiveCell.Row
End If
If ActiveCell.Column = FirstDataCol Then
LastHighlightedCol = ActiveCell.Column
End If
'set red highlight in column title if needed
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(FirstDataRow - 2, _
LastHighlightedCol - 1).Interior.ColorIndex = Red
Else
'find last selected cell in data range
'THIS! could take a while for large data area!!
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":" & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim currentRow As Long
Dim currentCol As Long
Dim DataRange As String
Dim anyCell As Object

If Target.Row < FirstDataRow Or Target.Column < FirstDataCol Then
Exit Sub ' not in data area
End If
Application.EnableEvents = False
currentRow = Target.Row
currentCol = Target.Column
'remove highlight from previously selected cell
'if last selected cell is outside of the data range then
'LastHighlightedRow and LastHighlightedCol will be zero
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(LastHighlightedRow - 1, _
LastHighlightedCol - 1).Interior.ColorIndex = xlNone
Else
'find a red cell in data area?
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":"
DataRange = DataRange & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
ActiveCell.Interior.ColorIndex = Red ' highlight selected cell
'changed columns? as moving across sheet?
If currentCol < LastHighlightedCol Then
If LastHighlightedCol 0 Then
'reset previous Row 5 title shading
SetColumnTitleShading LastHighlightedCol - 1
End If
Range("A1").Offset(FirstDataRow - 2, _
currentCol - 1).Interior.ColorIndex = Red
LastHighlightedCol = currentCol
End If
'changed rows? as moving up/down sheet?
If currentRow < LastHighlightedRow Then
If LastHighlightedRow 0 Then
Rows(LastHighlightedRow).Interior.ColorIndex = xlNone
End If
Range("A" & currentRow & ":D" & _
currentRow).Interior.ColorIndex = Red
LastHighlightedRow = currentRow
End If
Application.EnableEvents = True

End Sub

Private Sub SetColumnTitleShading(WhichColumn As Long)
If Range("A1").Offset(LastPossibleRow - 1, _
WhichColumn).End(xlUp).Row < FirstDataRow Then
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = xlNone
Else
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = Orange
End If
End Sub


"Ed" wrote:

Hello I am working in Excel 2007, on a long Table from $A$1:$DO$9 so far
which will be growing vertically with each entry, and maybe in some special
cases new columns will be added but not often at all. Data is located from
E6:DO9, the rest of the cells in the Table are just Headers and so on. So
what I would like to have is the following:

A)
When the active cell is inside E6:D09, for example:
G6, to have $A6:$D6 highlighted in red lets say, and G$5 in red as well...
if I move to cell G7, I would have $A7:$D7 and G$5 in red, and if I move to
H7, I would have$A7:$D7 and H$5 higlighted.

B)
Aside from that I guess this part is a bit more complicated, I hope it is
possible as well... Each row is a entry, so on a row I will have several
cells with data (a number, the rest are empty) so on the current row to
highlight the titles (row 5) which have data in the current row, in orange.

So in summary, the idea is to highlight the titles (Row 5) that have data on
the active row in orange and at the same time to highlight the current title
(Row 5) and A:D of the current row in Red... I really hope I was able to
explain myself...

Thank you very much for your attention!

Yes I think you got it this time! Yeah I guessed the orange part was a bit
hard to explain but yeah that is the way I ment it to work...

Im sent you the file I am working with if it is ok with you to check it...
Download it from:

http://www.yousendit.com/transfer.ph...86458D67AB123E

When I saved it for Excel 97-2003 I hope it works fine, this is not final
yet but the what we are talking about is ok. I apologize because it is
written in spanish, but I guess you get the idea.

Tab "Generador" is where we are at. The way this file works is that under
"Generator Tab" I type in some quantities of the materials I am giving away
to contractors and that generates me a delivery form (in tab "Formato") for
printing to be signed by whoever is recieving the materials (E3 in "Formato"
has a dropdown to choose the form I am looking for).

In this file I sent you, for example, when active cell is N6, then $A6:$D6,
N6 and N$5 would be red and the "titles" of the other cells in row 6 that
have data would be orange: Y$5, AJ$5, AU$5 and CV$5. When the active cell is
now S7, then $A7:$D7, S7 and S$5 would be in red, and now: T$5, CV$5 and CW$5
would be in orange but the ones from the previous selected active cell are no
longer highlighted just the other cells containing data on the active row

so what do you think?

I've downloaded the file, haven't examined it yet. The language doesn't
bother me much, as with most help given here one doesn't have to know so much
what the data itself actually is, just what to do with it.

I'll plug away at this for a bit and see what it takes to change it.

"Ed" wrote:

Yes I think you got it this time! Yeah I guessed the orange part was a bit
hard to explain but yeah that is the way I ment it to work...

Im sent you the file I am working with if it is ok with you to check it...
Download it from:

http://www.yousendit.com/transfer.ph...86458D67AB123E

When I saved it for Excel 97-2003 I hope it works fine, this is not final
yet but the what we are talking about is ok. I apologize because it is
written in spanish, but I guess you get the idea.

Tab "Generador" is where we are at. The way this file works is that under
"Generator Tab" I type in some quantities of the materials I am giving away
to contractors and that generates me a delivery form (in tab "Formato") for
printing to be signed by whoever is recieving the materials (E3 in "Formato"
has a dropdown to choose the form I am looking for).

In this file I sent you, for example, when active cell is N6, then $A6:$D6,
N6 and N$5 would be red and the "titles" of the other cells in row 6 that
have data would be orange: Y$5, AJ$5, AU$5 and CV$5. When the active cell is
now S7, then $A7:$D7, S7 and S$5 would be in red, and now: T$5, CV$5 and CW$5
would be in orange but the ones from the previous selected active cell are no
longer highlighted just the other cells containing data on the active row

so what do you think?

This version is very tailored to your specific workbook. While the basics
are well covered in the code published earlier, code changes in this one are
specific to your layout and setup.

http://www.jlathamsite.com/uploads/Rev01_forEd.zip

has 2 files in it: modified and renamed copy of the file you sent to me with
the code attached to the specific worksheet. Second file is just a .txt file
that explains some of the changes made and how they affect the operation.

I took your conditional formatting within the data range and actually made
use of it - if you choose cells that don't have conditional formatting in
them (as down below row 13 in this workbook) it doesn't take any action
either. So it's only doing all the work while you're actually within the
data range iteslf.

"Ed" wrote:

Yes I think you got it this time! Yeah I guessed the orange part was a bit
hard to explain but yeah that is the way I ment it to work...

Im sent you the file I am working with if it is ok with you to check it...
Download it from:

http://www.yousendit.com/transfer.ph...86458D67AB123E

When I saved it for Excel 97-2003 I hope it works fine, this is not final
yet but the what we are talking about is ok. I apologize because it is
written in spanish, but I guess you get the idea.

Tab "Generador" is where we are at. The way this file works is that under
"Generator Tab" I type in some quantities of the materials I am giving away
to contractors and that generates me a delivery form (in tab "Formato") for
printing to be signed by whoever is recieving the materials (E3 in "Formato"
has a dropdown to choose the form I am looking for).

In this file I sent you, for example, when active cell is N6, then $A6:$D6,
N6 and N$5 would be red and the "titles" of the other cells in row 6 that
have data would be orange: Y$5, AJ$5, AU$5 and CV$5. When the active cell is
now S7, then $A7:$D7, S7 and S$5 would be in red, and now: T$5, CV$5 and CW$5
would be in orange but the ones from the previous selected active cell are no
longer highlighted just the other cells containing data on the active row

so what do you think?

Hello again,
I opened your file and read the instructions, I got it... Thank you very
much for your time and patience! Really this is working exactly what I was
looking for, it works great! its very useful...

,Ed

Glad to hear it. I hope the explanations were clear enough for you to
maintain and adapt it for your future use as may be required.

"Ed" wrote:

Hello again,
I opened your file and read the instructions, I got it... Thank you very
much for your time and patience! Really this is working exactly what I was
looking for, it works great! its very useful...

,Ed

This is a great code. I have a similar problem, and I'd like to know if you
could "tailor" this code to my situation. I tried changing the code, but I'm
not a programmer and could only succeed in changing the color from red to
something else.

I have data from C3:AT54. My titles are in A3:A54 for the row titles and
column titles are C1:AT1. I would like to bold the current row/column titles
and increase the font size by 2 pts. No color changes. This would apply
only when I'm selecting inside the data range.

Another thing that's not quite as big of a deal but could be cool if it
could be worked around is that I have a couple of row titles and column
titles intermittently throughout the data range. I have these rows/columns
greyed and the titles bolded, and I would like to make sure that they don't
get changed as I'm clicking throught the data range, ie if I happen to click
in that row/column and then it automatically changes my desired setting.

Can you help me?

"JLatham" wrote:

A working copy of this that you can upload and use is he
http://www.jlathamsite.com/uploads/A...ight_forEd.xls

All of this code goes into the specific sheet's code module. No doubt that
the editor here is going to break some lines so might be better to cut and
paste from the sample into your workbook. I've tried to anticipate the
breaks, but ... Instructions for getting into a worksheet's code module can
be found he http://www.jlathamsite.com/Teach/WorksheetCode.htm

The sheet name is not important, and the code automatically adjusts for any
added rows of data, and as long as your titles in row 5 extend on out without
a break (empty cell), the highlighting of that row will work properly.

Option Explicit
Dim LastHighlightedRow As Long
Dim LastHighlightedCol As Long
Const FirstDataRow = 6
Const FirstDataCol = 5
Const LastPossibleRow = 65536 ' change for Excel 2007
Const Red = 3
Const Orange = 46

Private Sub Worksheet_Activate()
Dim LastDataColumn As Long ' for Excel 2007
Dim ColumnToExamine As Long ' again for Excel 2007
Dim DataRange As String
Dim anyCell As Object
Application.EnableEvents = False
'set up Orange highlighting of used columns
'could take some time, depending on speed of system
LastDataColumn = Range("A" & _
FirstDataRow - 1).End(xlToRight).Column
For ColumnToExamine = FirstDataCol To LastDataColumn
SetColumnTitleShading ColumnToExamine - 1 ' using as an Offset
Next
'initialize values if possible
If ActiveCell.Row = FirstDataRow Then
LastHighlightedRow = ActiveCell.Row
End If
If ActiveCell.Column = FirstDataCol Then
LastHighlightedCol = ActiveCell.Column
End If
'set red highlight in column title if needed
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(FirstDataRow - 2, _
LastHighlightedCol - 1).Interior.ColorIndex = Red
Else
'find last selected cell in data range
'THIS! could take a while for large data area!!
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":" & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim currentRow As Long
Dim currentCol As Long
Dim DataRange As String
Dim anyCell As Object

If Target.Row < FirstDataRow Or Target.Column < FirstDataCol Then
Exit Sub ' not in data area
End If
Application.EnableEvents = False
currentRow = Target.Row
currentCol = Target.Column
'remove highlight from previously selected cell
'if last selected cell is outside of the data range then
'LastHighlightedRow and LastHighlightedCol will be zero
If LastHighlightedRow 0 And LastHighlightedCol 0 Then
Range("A1").Offset(LastHighlightedRow - 1, _
LastHighlightedCol - 1).Interior.ColorIndex = xlNone
Else
'find a red cell in data area?
DataRange = Range("A1").Offset(FirstDataRow - 1, _
FirstDataCol - 1).Address & ":"
DataRange = DataRange & _
Selection.SpecialCells(xlCellTypeLastCell).Address
For Each anyCell In Range(DataRange)
If anyCell.Interior.ColorIndex = Red Then
LastHighlightedRow = anyCell.Row
LastHighlightedCol = anyCell.Column
Exit For ' quit, we found it
End If
Next
End If
ActiveCell.Interior.ColorIndex = Red ' highlight selected cell
'changed columns? as moving across sheet?
If currentCol < LastHighlightedCol Then
If LastHighlightedCol 0 Then
'reset previous Row 5 title shading
SetColumnTitleShading LastHighlightedCol - 1
End If
Range("A1").Offset(FirstDataRow - 2, _
currentCol - 1).Interior.ColorIndex = Red
LastHighlightedCol = currentCol
End If
'changed rows? as moving up/down sheet?
If currentRow < LastHighlightedRow Then
If LastHighlightedRow 0 Then
Rows(LastHighlightedRow).Interior.ColorIndex = xlNone
End If
Range("A" & currentRow & ":D" & _
currentRow).Interior.ColorIndex = Red
LastHighlightedRow = currentRow
End If
Application.EnableEvents = True

End Sub

Private Sub SetColumnTitleShading(WhichColumn As Long)
If Range("A1").Offset(LastPossibleRow - 1, _
WhichColumn).End(xlUp).Row < FirstDataRow Then
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = xlNone
Else
Range("A1").Offset(FirstDataRow - 2, _
WhichColumn).Interior.ColorIndex = Orange
End If
End Sub


"Ed" wrote:

Hello I am working in Excel 2007, on a long Table from $A$1:$DO$9 so far
which will be growing vertically with each entry, and maybe in some special
cases new columns will be added but not often at all. Data is located from
E6:DO9, the rest of the cells in the Table are just Headers and so on. So
what I would like to have is the following:

A)
When the active cell is inside E6:D09, for example:
G6, to have $A6:$D6 highlighted in red lets say, and G$5 in red as well...
if I move to cell G7, I would have $A7:$D7 and G$5 in red, and if I move to
H7, I would have$A7:$D7 and H$5 higlighted.

B)
Aside from that I guess this part is a bit more complicated, I hope it is
possible as well... Each row is a entry, so on a row I will have several
cells with data (a number, the rest are empty) so on the current row to
highlight the titles (row 5) which have data in the current row, in orange.

So in summary, the idea is to highlight the titles (Row 5) that have data on
the active row in orange and at the same time to highlight the current title
(Row 5) and A:D of the current row in Red... I really hope I was able to
explain myself...

Thank you very much for your attention!