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

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

--
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"


"Martin" wrote:

> 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
>
> --
> Regards,
>
> Martin

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

Martin


"K Dales" wrote:

> 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"
>
>
> "Martin" wrote:
>
> > 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
> >
> > --
> > Regards,
> >
> > Martin