how to highlight current row & column automatically by a color

[Guest]

I'm using Excel 2003, can u plz help me out that how to highlight current row
& column with a color automatically to identify my position. Though excel
help us by highlighting column & row number, but i want whole column & row to
be highlighten to better finding my cell position and contents. So When I
change my cell position, the highlighted row & column move appropriately.

 

[JE McGimpsey]

Take a look at

http://cpearson.com/excel/rowliner.htm

 

[Guest]

This is a cool add-in, but is there a way to write something like this into
the code? I was thinking I could do it by somehow getting the current row
number and column number in 2 separate cells, and then apply a conditional
format to the column titles and row titles that bolds/colors/etc if the cell
value matches the column/row number. But I don't know how to get the current
row or column numbers. Any suggestions?

 

[JE McGimpsey]

You can get the current row/column numbers using the CELL function, so
you could use the CF:

Formula is =OR(CELL("row")=ROW(),CELL("col")=COLUMN())

however, you'd need to cause a calculation (either via F9, or perhaps
via the _SelectionChange event) to get the CF to update.

And I suspect if you apply it to a large area, you're going to take a
performance hit.

 

[Guest]

Thanks for your response. I posted my question in more detail on the thread
"Need a "Tailored" Highlighting Macro". There's some really good code in
there, I'm just wondering if it can be changed to fit my desired highlighting
format. If you have any feedback to that thread, please respond on it.

 

[JE McGimpsey]

If you have any feedback to that thread, please respond on it.

I'll pass. If you waste my time by starting another thread, rather than
responding to this one, I won't waste more trying to find the new one...

 

[Guest]

Here's my question in more detail. I'm sorry, I would never intend on
wasting anyone's time.

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?

 

[JE McGimpsey]

One way:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnBASEFONTSIZE As Long = 10
Const cnSELECTEDFONTSIZE As Long = 12
Const csCOLHEADS As String = "C1:AT1"
Const csROWHEADS As String = "A3:A54"
Dim rFormat As Range

With Union(Range(csCOLHEADS), Range(csROWHEADS)).Font
.Bold = False
.Size = cnBASEFONTSIZE
End With
On Error Resume Next
Set rFormat = Intersect(Range(csCOLHEADS), Target.EntireColumn)
If Not rFormat Is Nothing Then
With rFormat.Font
.Bold = True
.Size = cnSELECTEDFONTSIZE
End With
End If
Set rFormat = Intersect(Range(csROWHEADS), Target.EntireRow)
If Not rFormat Is Nothing Then
With rFormat.Font
.Bold = True
.Size = cnSELECTEDFONTSIZE
End With
End If
End Sub

 

[Guest]

Thank you for this code. It's very close to what I need. Is it possible for
the code to treat a subtitle column as non-data cells? The same way it
treats A1:B2? Currently, A1:B2 are non-data cells, and when I click on them,
the titles all retain their original formatting. I have a few intermittent
headings (as both column titles and row titles) that I would like to format
differently from the rest of the titles. So it would look like this:
A B C D E
F G
1 *C.Head1 C.Title1a C.Title1b *C.Head2 C.Title2a
C.Title2b
2 *R.Head1
3 R.Title1a
4 R.Title1b
5 *R.Head2
6 R.Title2a
7 R.Title2b

The * denotes a heading with a different formatting (greyed, > font size,
bold) than the surrounding titles. I would like these cells to remain the
same while all of the rest of the titles change exactly as you coded.
However, the headings are not periodically spaced as shown above. I'd like
the code to somehow check the cell formatting before changing it. Can this
be done?