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 |
Change cell colour for a cell or range within a predefined range
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 |
Change cell colour for a cell or range within a predefined ran
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 |
All times are GMT +1. The time now is 11:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com