PDA

View Full Version : Change color of cell/cells



Tom Jones
01-03-2018, 02:19 PM
Howdy,

How could I change color of a selected cell (or range of selected cells) in this way:
When press a button first time, selected cell color became GREEN, next press button, cell color became YELLOW, then
cell will be RED, next will be ORANGE, then cell will be normal color (xlNone).

Thanks.

Paul_Hossler
01-03-2018, 03:46 PM
This is the code I tied to a button on the attached XLSM.

It looks at the first cell in the selected range and applies the next color to the selection

If only one cell is selected, it only does that one




Option Explicit
'How could I change color of a selected cell (or range of selected cells) in this way:
'When press a button first time, selected cell color became GREEN, next press button, cell color became YELLOW, then
'cell will be RED, next will be ORANGE, then cell will be normal color (xlNone).

Sub CycleColors()
If Not TypeName(Selection) = "Range" Then Exit Sub

With Selection
Select Case .Cells(1, 1).Interior.ColorIndex
Case xlColorIndexNone
.Interior.ColorIndex = 4 ' green
Case 4
.Interior.ColorIndex = 6 ' yellow
Case 6
.Interior.ColorIndex = 3 ' red
Case 3
.Interior.ColorIndex = 44 ' orange
Case 44
.Interior.ColorIndex = xlColorIndexNone ' none
End Select
End With
End Sub

Tom Jones
01-04-2018, 02:40 AM
Excellent. Thank you Paul.