-1

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
RubberDuck
  • 11,933
  • 4
  • 50
  • 95
Tom36
  • 152
  • 1
  • 4
  • 25
  • Couldn't you just do "ActiveCell.Offset(0,1).interior.colorindex = 3"? – OpiesDad Mar 13 '15 at 16:33
  • possible duplicate of [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – RubberDuck Mar 13 '15 at 16:34

1 Answers1

0

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.

eirikdaude
  • 3,106
  • 6
  • 25
  • 50