Search code examples
excelvba2d-games

Excel VBA offset without activating the cell

I am trying to change the colour of a cell. I have code that stops a cell with a black background being activated. This works well however I want to change the background of some of these cells to Red on a button click event.

When the button is clicked I want all cells around it to turn red. Due to not being able to select the cells I can not do this. If I use something like Range("A1").Value = "Hi" (this is a cell with a black background), Hi will appear in there, i think this is because I have not activated the cell just changed its value.

Is there a way to Offset from the active cell and change the colour of the cells around it without activating/selecting those cells?

Code in the Worksheet_SelectionChange is: 

'Stops users selecting a Black cell. If they try they are returned to     their previous cell

If Target.Cells(1, 1).Interior.ColorIndex = 1 Then 
   OldRange.Select  
    'MsgBox "H" 
Else 
    Set OldRange = Target 
End If

My code in the Button Click is:

If Range("AL7").Value = "Bomb" Then 
 MsgBox "BOOM, BANG, KABOOM!!" 
ActiveCell.Interior.ColorIndex = 3 

ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Select 
ActiveCell.Interior.ColorIndex = 3 

ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate 
ActiveCell.Interior.ColorIndex = 3 

ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate 
ActiveCell.Interior.ColorIndex = 3 

ActiveCell.Offset(rowOffset:=0, columnOffset:=-1).Activate 
ActiveCell.Interior.ColorIndex = 3
Solution

  • You don't have to select a cell to change it properties, actually that is considered bad practice, and fraught with unintended consequences by many.

    Instead you could do something like this:

    ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Interior.ColorIndex = 3

    And have focus staying in the cell you were in.