Change cell colour for a cell or range within a predefined range

[Guest]

Dear all,

The code below is working to change the cell colour in one cell only within
range(C4:C12). A message box appears when the user tries outside the range.

Is it possible to change the code to enable change of a range of cells and
also lets say C5 and C8 at the same time - within the range(C4:C12). The
message box should appear when trying to change colour in lets say
range(C6:C15).

Sub White()

If Not Intersect(ActiveCell, [C4:C12]) Is Nothing Then
ActiveCell.Interior.ColorIndex = 0
Else
MsgBox "It is only possible to change colour in C4 down to C12 "
& vbNewLine & "in Column C only!", vbCritical, "Cell Colour Change"
End If

End Sub

 

[Guest]

Yes, you can use the Selection object to refer to the selected cells, even if
not a continuous range, and then step through the individual cells with a For
Each... loop:

Sub White()
Dim ThisCell as Range, Oops as Boolean

For Each ThisCell in Selection.Cells
If Not Intersect(ThisCell, [C4:C12]) Is Nothing Then
ThisCell.Interior.ColorIndex = 0
Else
Oops = True
End If
Next ThisCell

If Oops Then MsgBox "It is only possible to change colour in C4 down to C12
" & vbNewLine & "in Column C only!", vbCritical, "Cell Colour Change"

 

[Guest]

That's great - thank you very much!
--
Regards,

Martin

Yes, you can use the Selection object to refer to the selected cells, even if
not a continuous range, and then step through the individual cells with a For
Each... loop:

Sub White()
Dim ThisCell as Range, Oops as Boolean

For Each ThisCell in Selection.Cells
If Not Intersect(ThisCell, [C4:C12]) Is Nothing Then
ThisCell.Interior.ColorIndex = 0
Else
Oops = True
End If
Next ThisCell

If Oops Then MsgBox "It is only possible to change colour in C4 down to C12
" & vbNewLine & "in Column C only!", vbCritical, "Cell Colour Change"

Dear all,

The code below is working to change the cell colour in one cell only within
range(C4:C12). A message box appears when the user tries outside the range.

Is it possible to change the code to enable change of a range of cells and
also lets say C5 and C8 at the same time - within the range(C4:C12). The
message box should appear when trying to change colour in lets say
range(C6:C15).

Sub White()

If Not Intersect(ActiveCell, [C4:C12]) Is Nothing Then
ActiveCell.Interior.ColorIndex = 0
Else
MsgBox "It is only possible to change colour in C4 down to C12 "
& vbNewLine & "in Column C only!", vbCritical, "Cell Colour Change"
End If

End Sub