Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count specific colour from cell range with varied colors? | Excel Worksheet Functions | |||
Count filled colour in cell in given range | Excel Discussion (Misc queries) | |||
Conditional Format - Formula to Colour Every 3rd Cell in Offset Range | Excel Discussion (Misc queries) | |||
Change even rows to a predefined colour | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming |