Changing target cell formatting

Hey, guys. What's up?

I'd like to know if it's possible to do something like this...

1) I click or simply move the cursor to a certain cell, say, B10.
2) That cell (B10) gets highlighted (by highlighted, I mean, its background color gets changed into red, for instance).
3) I leave the cell (B10) by clicking or moving the cursor to another cell, say, B11.
4) The cell I left (B10) gets its formatting back; same cell formatting it had before I clicked on it.

teylyn has already helped me with something similar. Take a look, please.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lrow As Long
On Error Resume Next
lrow = Range("A18").End(xlDown).Row
Application.ScreenUpdating = False
If Intersect(Target, Range("A18", Range("G" & lrow))) Is Nothing Then
    Rows("18:" & lrow).RowHeight = 13.25
Else
    Rows("18:" & lrow).RowHeight = 13.25
    Target.RowHeight = 20
End If
Application.ScreenUpdating = True
End Sub

By the way, it's easy to do this when I already know the target cell formatting. However, if I have a big range of cells with different formatting, it's more complicated (to me, of course). That's why I want to know if it's possible to do it automatically for the whole formatting (background color, font color, borders, everything).

It would be something like... When I click a cell, Excel copies the formatting of that cell and then changes its background color (so it gets highlighted). Then, when I leave that cell, Excel pastes that formatting back on it.

I hope you can help. I really don't know how to do that.

Thank you very much for your attention, guys.

brunces

Maybe something like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static col As Long, adr As String
    If Target.Count = 1 Then ' So it works only if 1 cell is selected
        If adr = "" Then
            'reset your range to default format here?
            
        Else
            Range(adr).Interior.Color = IIf(col = 16777215, xlNone, col)
        End If
        col = Target.Interior.Color
        adr = Target.Address
        Target.Interior.Color = 255 ' highlight selected cell red
    End If
End Sub

There were several mistakes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count <> 1 Then Exit Sub ' <- use Count, not CountLarge. If you want to exit, then it should be <> 1, not = 1

    Static lngColor As Long, lngLastRow As Long, strAddress As String

    On Error Resume Next

    lngLastRow = Range("A5").End(xlDown).Row

    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("A5", Range("G" & lngLastRow))) Is Nothing Then
        If strAddress = "" Then
            'do not exit sub here. If you exit sub, the address would never get saved, not even for the first time.
        Else
            Rows("5:" & lngLastRow).RowHeight = 15
            Range(strAddress).Interior.Color = IIf(lngColor = 16777215, xlNone, lngColor)
        End If
        strAddress = Target.Address
        lngColor = Target.Interior.Color
        Target.Interior.Color = 13421823
        Target.RowHeight = 25
        
    Else ' Added this segment. necessary to reset color if clicking out of "designated range"
        If strAddress = "" Then
            'do not exit sub here.
        Else
            Rows("5:" & lngLastRow).RowHeight = 15
            Range(strAddress).Interior.Color = IIf(lngColor = 16777215, xlNone, lngColor)
        End If
        
    End If
    Application.ScreenUpdating = True

End Sub

Just out of curiosity, millz, could you please explain this to me?

IIf(lngColor = 16777215, xlNone, lngColor)

What does this function do? Mainly the numer 16777215. Why that specific number?

Thanks again.

brunces

--- EDITED ---

By the way, I'm having problems with logging in/posting/editing here. Is this forum facing any kind of problems? :/

That number represents white. Normally nobody would purposefully colour a cell white, so I take it as anything with that is the default coloured cell. But if you leave that part out, the code would colour a cell back to white, and you may lose some default cell borders. Just manually fill an empty cell white and you will know what I mean.

It seems using .CountLarge would solve the problem. Apparently it's not doing what it's described as in the help file. My bad on that.

Range.CountLarge Property - Counts the largest value in a given range of values. Read-only Variant.

It's actually working the same way as .Count, but with a larger capacity.