Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cell-position Conditional Formatting

    My eyes aren't as sharp as they once were, and I was thinking that it would be nice to change the shading of the column and row that the cellpointer was currently in. That got me to thinking about conditional formatting, and changing the background colour of the cells leading to the active cell.

    Is there some way to determine what the active cell is, and pass this on to conditional formatting? IE, if D7 is the active cell, change the background colour of D1.D6 and A7.C7, thus creating a visual "assist" to quickly locate the active cell and make sure that I am working in the correct row and column?

    (Better yet would be if this could be accomplished without using up conditional formatting, but just use this as a way of changing the display window only.)

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    David,
    this doesn't use conditional formatting, but I think will do the job if you want to use a VBA solution. Put it in either the workbook module or the sheet module depending on whether you want it for just one sheet or for the whole book. I haven't tested this but it should work - I recall posting similar code in the old lounge but don't know whether anyone's got a copy still - also note that it doesn't take account of any preapplied formatting - let me know if you want that and I'll dig it out.

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Static OldCell As Range
    If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = 0
    Target.Interior.ColorIndex = 6
    Set OldCell = Target
    End sub

    Brooke

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    That is pretty nifty Brooke!

    Thanks,

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    It took a bit of work, but I managed to get some results from your code. The active cell is now a nice shade of yellow. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    This is close to what I was thinking of, but not quite. Is there a way to extend the effect to include the entire row or column (or both) that the active cell is in? I'm thinking more along the lines of a "rule line" effect, not just a single highlighted cell. The minimal "raised & bold" effect that Excel uses in the Row and Column headers sometimes is not quite enough of a visual aid.

    Blame it on toooo many years of staring at monitors under poor lighting conditions. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    David, if you replace every occurrence of Interior in Brroke's code with EntireRow.Interior, the full row will highlight in yellow on selection. The problem with this is that if you have any existing cell coloring in a worksheet, such coloring is lost after using this type of code. It would be relatively easy to adapt it to restore the color for individual cells, or indeed full rows of the same color. You run into problems where the cells of a row have different colors.

    Andrew C

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    I've been running a spreadsheet at 25% and know what you mean about the raised and bold thing. The code below is as far as I got - not wanting the whole line highlighted, but wanting the first visible cell in any row highlighted, and trying to take account of prior formatting, as Andrew points out. It works to an extent. The trouble is that I get yellow dots appearing whenever I save the workbook, close and reopen it in the position of the active cell and the leftmost visible cell in the current row.

    '################################################# #######################################
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Static OldCell As Range
    Static OldCellLeft As Range
    Static OldCellColorIndex As Integer
    Static OldCellLeftColorIndex As Integer
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    If Not OldCell Is Nothing Then OldCell.Interior.ColorIndex = OldCellColorIndex
    If Not OldCellLeft Is Nothing Then OldCellLeft.Interior.ColorIndex = OldCellLeftColorIndex

    OldCellColorIndex = Target.Interior.ColorIndex
    OldCellLeftColorIndex = ActiveSheet.Cells(Target.Row, Windows(1).VisibleRange.Column).Interior.ColorInde x

    Target.Interior.ColorIndex = 6
    ActiveSheet.Cells(Target.Row, Windows(1).VisibleRange.Column).Interior.ColorInde x = 6

    Set OldCell = Target
    Set OldCellLeft = ActiveSheet.Cells(Target.Row, Windows(1).VisibleRange.Column)

    End Sub

    I suspect all I need to do is throw some code into the workbook_before_close/save events resetting the cell colours and setting the variables to nothing, but I just haven't got that far.

    Brooke

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    Here is my two pence worth:

    - select all cells
    - format, conditional format
    Formula is:
    =OR((ROW(INDIRECT("rc",FALSE))=ROW(INDIRECT(CELL(" address")))),(COLUMN(INDIRECT("rc",FALSE))=COLUMN( INDIRECT(CELL("address")))))
    - Select the shading you like
    - OK

    This does not update automatically, so a selection_change procedure is needed:

    Add this to the worksheet module:

    Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Target.Calculate
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    Very neat. I think I'll borrow that.

    Brooke

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    Hi Brooke,

    <<Very neat. I think I'll borrow that.>>

    Be my guest.

    Question: any chance that an On-line reader could capture posts from here and send replies to here?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cell-position Conditional Formatting

    On line reader? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    <<On line reader>>

    Ooooh, silly me. I meant an Off-line reader of course.
    Enables you to download messages, disconnect, read & answer, reconnect and upload them.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Cell-position Conditional Formatting

    Try <A target="_blank" HREF=http://www.wopr.com/w3thelp/help_10.html#Notification>Help: Email Notifications/Subscriptions </A> If you sign up you can subscribe to the daily digest of information delivered via email. Not quite what you were asking for - but close.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell-position Conditional Formatting

    Thanks Catharine.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •