Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Active Cell Formatting

  1. #1
    Guest

    Default

    Hi, I didn't see this already posted, so here goes -
    I've got cells B1:F9 summarising large worksheet, and have been manually formatting this to highlight the areas that I'm currently working on. However, I'd like be able to click onto any of these cells and have Excel format the range for that row (ie I click on C5, Excel automatically formats B5:F5), and take this off again when I leave the cell. Does anyone know how to do this?
    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi there

    This event macro code will get you started.
    (right click sheet tab, left click View Code, then paste this)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row >= 1 Then
    If Target.Row < 10 Then
    If Target.Column > 1 Then
    If Target.Column < 7 Then
    Range("B1:F9").Interior.ColorIndex = xlNone
    Range(Range("B" & ActiveCell.Row, "F" & ActiveCell.Row).Address).Interior.ColorIndex = Range("A1").Value
    End If
    End If
    End If
    End If
    End Sub

    Put a number in A1 (say 4). This will highlight your target row in green.
    As you can see, I have forgotten the intersect code that restricts it to your range so I have had to use 4 if statements, but it still seems to work okay.

    Hope this is what you were trying to achieve
    Regards
    Derek

  3. #3
    Guest

    Default

    Thanks for the quick reply Derek
    I've just amended the coding to read:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row >= 1 Then
    If Target.Row < 10 Then
    If Target.Column >= 1 Then
    If Target.Column < 6 Then
    Range("A1:E9").Interior.ColorIndex = xlNone
    Range("A1:E9").Font.ColorIndex = 1
    Range("A1:E9").Font.Bold = False
    Range(Range("A" & ActiveCell.Row, "E" & ActiveCell.Row).Address).Interior.ColorIndex = 37
    Pattern = xlSolid
    Range(Range("A" & ActiveCell.Row, "E" & ActiveCell.Row).Address).Font.ColorIndex = 11
    Range(Range("A" & ActiveCell.Row, "E" & ActiveCell.Row).Address).Font.Bold = True
    End If
    End If
    End If
    End If
    End Sub

    and it now does just what I want it to do!

  4. #4
    Guest

    Default

    Ok, to really go for this -

    The code above will overwrite any prior manual formatting in the cells - if I've formatted one of the range cells and click into it, and then out again, it returns to the "no format" line of the code. Can the code be written to return to the cell's prior state?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    If your cells already have other formatting, you need to take a different approach. Adapt the following event macro to your spreadsheet. This uses hidden column A. It first clears A then inserts the integer 1 against the row of the active cell (provided the active cell is within the range you have specified). Then select your specific range and apply conditional formatting using the formula =$A1=1
    This temporary formatting will apply while there is a 1 in column A and your original formatting will be restored when the active cell moves to a different line


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    x = ActiveCell
    y = ActiveCell.Column - 1

    If Range("B1").Value = 77 Then
    If Target.Row >= 1 Then
    If Target.Row < 10 Then
    If Target.Column >= 1 Then
    If Target.Column < 6 Then
    Range("A:A").ClearContents
    ActiveCell.Offset(0, -y).Value = 1
    End If
    End If
    End If
    End If
    End If
    End Sub

    "If Range("B1").Value = 77 Then...." is just my way of having a switch to turn the macro on or off. (ie by deleting 77 from cell B1)
    The reason for this is that, should you need to use it, the paste function will not work on the specified range while this macro is active.

    Have fun
    Derek


  6. #6
    Guest

    Default

    Derek, thanks again, this really works well(once I had turned calculation back to automatic!)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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