Highlight the all row and all column of active cell ? 4

[mirceapop14]

How can i highlight the actual row and actual column of an active cell with VBA ? It will be usefull to highlight with different color all cells from 1 to actual cell per row and full column from 1 to actual cell.
Thanks for any suggestions.

 

[Loomah]

Something like this should do as a starter

With ActiveCell
    Range(Cells(.Row, 1), Cells(.Row, .Column)).Interior.ColorIndex = 3
    Columns(.Column).Cells.Interior.ColorIndex = 15
End With

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[mirceapop14]

Thanks, but where i will put the code ?
In a module ?
I vote for you!

 

[Loomah]

Hi
It all depends on how you want to call the code or when you want it to run.

If you create a commandbutton on your sheet you can assign the code to the click event of that button. As I say, it all depends on your actul requirements.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[mirceapop14]

I wish to work that when i navigating in worksheet with directional keys.
Thanks again!

 

[xlbo]

this would need to go in the worksheet SELECTION CHANGE EVENT

You would also need code to remove the previous highlighted row/column

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mirceapop14]

An example, please, for remove the previous highlighted row/column ....
Thanks !

 

[Loomah]

Hi
Here's one option you could use, but it's very rough and ready!

Please note that this will not work until the worksheet as been activated, ie if you add the code to the worksheet module while the worksheet is active then select a different cell it will crash.

Option Explicit
Dim lRow As Long
Dim iCol As Integer

Private Sub Worksheet_Activate()
    'initialise values for 'previous' row & col
    lRow = ActiveCell.Row
    iCol = ActiveCell.Column
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    'remove previous format
    Range(Cells(lRow, 1), Cells(lRow, iCol)).Interior.ColorIndex = xlColorIndexNone
    Columns(iCol).Interior.ColorIndex = xlColorIndexNone
    
    'add new formatting
    With Target
        Range(Cells(.Row, 1), Cells(.Row, .Column)).Interior.ColorIndex = 3
        Columns(.Column).Cells.Interior.ColorIndex = 15
        lRow = .Row
        iCol = .Column
    End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

mirceapop14 - this is NOT a helpdesk - you are expected to be able to try things for yourself rather than just asking people for their knowledge - did you even LOOK at what you might need to do to clear cell colours ?? I doubt it because all that you needed is in Loomah's 1st post - that and a quick look in the helpfiles to check for how to set the colour to nothing...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mirceapop14]

Ok, i place the code as subroutine in code for the worksheet and is working now.
Thanks a lot guys !
Real experts again !

 

[mirceapop14]

I descovered another problem !

The code working but has deleted all colors of cells and rows in my worksheet..
Maybe some help will be good.

 

[Loomah]

Hi
If you have existing formatting (interior colours) in your spreadsheet they will be lost when the cells are highlighted. They won't reappear just because you remove the highlighting!

Checking every cell and remembering formatting would be ridiculous. The code I've already posted is slow enough.

There may be a way around this using conditional formatting but I don't thave the time it would take to look into it and try it out.

If you feel like trying yourself here are my initial thoughts for trying that route

1) Use the info on this site:-

http://www.cpearson.com/excel/banding.htm

and try to adapt

2) Record the process of applying conditional formatting. Is it possible? (I dunno, never done it!!)

3) Use Worsheet_SelectionChange event to trigger something

4) Pull my hair out as I realise I can't do it!!!!

Ignore the last bit, it may be possible, I just don't know. It'll give me something to do at lunchtime, maybe.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[mirceapop14]

Dear Loomah,
Thank you very much for your effort for helping me.
Sorry for disturbing some others, but this is a forum
and my discovered solution could be good to all others interested.

Thanks to all of you again !

 

[TonyJollans]

Hi mirceapop14,

I think what you're doing is just plain daft!! What version of Excel are you using? Isn't its highlighting of the column and row headers sufficient for you?

Anyway, here is an alternative that may help if you are not using Conditional Formatting anywhere else:

[blue]Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Cells.FormatConditions.Delete
    Cells.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR(And(Row()<" & ActiveCell.Row & ",Column()=" & ActiveCell.Column & ")" & _
           ",And(Row()=" & ActiveCell.Row & ",Column()<" & ActiveCell.Column & "))"
    Cells.FormatConditions(1).Interior.ColorIndex = 22

End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

Loomah,

Didn't see your post before I posted mine. Hope it saves some of your hair.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Loomah]

Tony
All hair safely intact!!

I'm quite pleased that the whole idea of using conditional formatting for this wasn't just pie in the sky. Although I don't know how long it would have taken me to come up with the formula required!!

I think that deserves a purple pointy pip from me.
Nice colour too:-D



;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[KenWright]

Just for the record, you could have just used Chip Pearson's rowliner addin.

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

One thing you really have to be aware of though is that you effectively kill any undo ability you had as a result of the code running all the time.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------