Change BackColor of CellRange where Focus Resides 3

[TheAceMan1]

Howdy All! . . .

I'm an Access DB programmer ... use to be Excel programmer, in the old days. I'll never swing the current company over to Access (they are stuck with Excel) and wish to perform a simple task.

I need to change the background color of a certain range of cells on any record where the focus resides. How do I do this! ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

Hi,

"where the focus resides" is the Selection for multiple cells or ActiveCell. Background is Interior property. Either Color or Colorindex, see VBA Help.

  Selection.Interior.ColorIndex = 3

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[nnett]

I think what you are after is:

Public old_row As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If old_row <> "" Then
    ActiveSheet.Range(Cells(old_row, 3), Cells(old_row, 5)).Interior.ColorIndex = xlNone
    old_row = ActiveCell.Row
    ActiveSheet.Range(Cells(old_row, 3), Cells(old_row, 5)).Interior.ColorIndex = 3
  Else
    old_row = ActiveCell.Row
    ActiveSheet.Range(Cells(old_row, 3), Cells(old_row, 5)).Interior.ColorIndex = 3
  End If
  
End Sub

Note that the code must be in the sheet

 

[combo]

If it is a temporary setting and selection is a good enough marker:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim aCell As Range
With Application
    .EnableEvents = False
    Set aCell = .ActiveCell
    .ActiveCell.EntireRow.Select
    aCell.Activate
    .EnableEvents = True
End With
End Sub

combo

 

[TheAceMan1]

All! . . .

Sorry to get back so late. Found what I needed here ... Highlight Active Row

Thanks for all your help!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

I think that you had a problem with the way that you phrased your initial question. It was very vague.

a certain range of cells on any record where the focus resides

It was not asking for "the ROW" or "the cells in the row of data". Rather "certain undefined range of cells"

Makes answering such a question a crap shoot, as two of the replies included "I think..." and "If it is..."

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TheAceMan1]

SkipVought . . .

Agreed! I did say I was an Access Programmer. Calling a row a record ... proves it!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[firefytr]

In a few code samples here, there is a lot of referencing which doesn't need to be there. If using an event code, and looking for the 'Activesheet', you can just use the 'Me' object, since that's where the code is residing. Referring to the entire row of the selection in a worksheet_selection event can be as simple as 'Target.EntireRow'. And of course any 'ActiveCell' reference can be 'Target', as passed in the event parameters.

Of course it's probably just me being nitpicky again!

Btw, good to be back on the forums. Haven't visited this particular one in quite a bit!

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

Zack! WHERE have you been???

Welcome back!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[firefytr]

Paramedic school my friend.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci