Active Cell Formatting

[Guest]

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

 

[Derek]

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

 

[Guest]

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!

 

[Guest]

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?

 

[Derek]

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

 

[Guest]

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