HELP PLEASE!!! How to delete applied format after navagating to different row

[photonicman]

I want to have the macro delete the color applied once a new "Row" is selected!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim RR As Range
Set RR = Target

Cells(RR.Row, 2).Interior.ColorIndex = 36
Cells(RR.Row, 3).Interior.ColorIndex = 36
Cells(RR.Row, 4).Interior.ColorIndex = 36

End Sub

 

[Fencliff]

Not sure if I got you right:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim RR As Range
    Set RR = Target

    If RR.Rows.Count = 1 And RR.Columns.Count = RR.Parent.Columns.Count Then

        Cells(RR.Row, 2).Interior.ColorIndex = 36
        Cells(RR.Row, 3).Interior.ColorIndex = 36
        Cells(RR.Row, 4).Interior.ColorIndex = 36

    End If

End Sub

 

[HippoCampus]

Good morning

This article http://msdn.microsoft.com/en-us/library/kw8z8fzk.aspx from the MS Knowledge base shows how to move the bold effect down a row each time a new row is added. You should be able to adapt this to suit your needs.

Hippo

 

[photonicman]

I don't want to highlight or select the entire row, I have conditional formats I cannot loose, I only wish to color the first the columns in the active row. I want those three columns to have their color deleted upon navigating away. I only want the color applied to the active row. I haven't been able to figure out how to do that.

 

[photonicman]

If anyone could help I would greatly appreciate it.

I need to have the 2nd, 3rd, and 4th cell highlighted / colored in the active row.

I need the color to be removed upon navagating to a new column.

The Code I first provided colors the three cells for me, but it doesnt remove the color when navagating to a new row. I just need a way to have the background color automatically cleared upon leaving each active row, and just in those 3 cells.

 

[pgc01]

It's confusing the part about removing the colour.

I understand when to paint the cells, when you select an entire row (I hope I got this one right).

What I don't understand is when to remove the colour:

1 - "I want to have the macro delete the color applied once a new "Row" is selected!"
If you select a new row, the code removes the colour from the previous selected row

2 - "I want those three columns to have their color deleted upon navigating away."
If you select any other range (row, cell, cells), the code removes the colour from the previous selected row

3 - I need the color to be removed upon navagating to a new column.
This one may have been a typo

This is an example for case 2. You select a row and the code applies the colour to the 3 cells. You leave the row (even if you don't select another whole row) and the code removes the colour.

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static RLastTarget As Range
 
    If Not RLastTarget Is Nothing Then
        Cells(RLastTarget.Row, 2).Resize(, 3).Interior.ColorIndex = xlColorIndexNone
    End If
 
    Set RLastTarget = Nothing
 
    If Target.Rows.Count = 1 And Target.Columns.Count = Columns.Count Then
        Cells(Target.Row, 2).Resize(, 3).Interior.ColorIndex = 36
        Set RLastTarget = Target
    End If
 
End Sub

 

[photonicman]

I'm sorry, yea, typo, I just wanted the color to be removed upon selection of a new row only (Not Cell or Column).

I have a sheet that has 80 coloumns with a frozen pane on the left, I want to have 3 cells on the frozen pane colored automatically on the current selected active row and I want to the color removed only when a new row is selected.

Example:

- I select cell F1, I want cells B1, C1, D1 to be colored

- I now select cell F2, I want cells B1, C1, D1 to be cleared (Color Removed), and I now want B2, C2, D2 to be Colored.

I'm sorry for the typo's and confusion, I was trying to figure out how to explain it myself, lol

Also, I couldnt get the code you provided to work... It doesnt seem to be doing anything in Excel 2007...

 

[photonicman]

Ohhh!! Wow, thats cool, I read your post again PGC01 and selected the row and it works flawlessly! Not what I wanted, but very neat! If you read my above post, I described what I was trying to do correctly, thanks so much! and I'm eagerly awaiting your reply!

Also, if you test my origional code, it colors the 3 cells I want correctly upon row selection, but it doesn't remove the color from those 3 cells upon selection of a new row and apply the color to the 3 cells in the current active row only.

 

[pgc01]

I just wanted the color to be removed upon selection of a new row only (Not Cell or Column).

Ok.

- Now the code paints the 3 cells upon the selection of a whole row, as before,

- but it will only remove the format of the cells when you select a new whole row, and not if you select cells or columns.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static RLastTarget As Range
 
    If Target.Rows.Count = 1 And Target.Columns.Count = Columns.Count Then
    
        If Not RLastTarget Is Nothing Then
            Cells(RLastTarget.Row, 2).Resize(, 3).Interior.ColorIndex = xlColorIndexNone
        End If
    
        Cells(Target.Row, 2).Resize(, 3).Interior.ColorIndex = 36
        Set RLastTarget = Target
        
    End If
End Sub

 

[photonicman]

Yea, thats not quite what I'm trying to get accomplished... Like the first post, the original code simply highlights 3 cells in an active row.

I don't want to have to select the entire row to have the 3 cells highlighted.

I simply want to be active in that row (If I select any cell within the row) be the reason for the 3 cells in the row be colored.

I don't want to have the color removed if I simply navigate the around within that particular row. If my active cell / selected cell is one row up or down, then I want the new row to have the 3 cells colored, but the previous row, the row I am no longer active in, have the 3 cells there cleared out.

Essentially the Original Code I posted worked, it just would keep adding the color to every row and not clear from the previous.... I once again am so sorry, I see how you could have misunderstood what I typed. Thanks so much for looking into this for me and once again I look forward to your response.