Program cell background color

[nebb]

My Excel worksheet has numerous cells that contain the same type formul
resulting in placing either *, or **, or *** in each of these cells
The data on the sheet is changed each week such that any cell, in an
particular week, might contain any of the three possibilities.
I would like to be able to run a macro that would insert one backgroun
color in each cell that contains one *, another color in those cell
that contain **, and still another background color in those cells tha
contain ***.
Any help would be greatly appreciated

 

[Dave Peterson]

As long as you don't exceed three possibilities, you could use a non-macro
approach.

You could use Format|Conditinal formatting...

Give it three conditions:
Cell value is ="*"
and a nice format
click Add
cell value is ="**"
and another nice format
click add
cell value is = "***"
and a last nice format

(Conditional formatting only supports 3 conditions, though.)

 

[Bob Phillips]

Here's a macro that can be easily extended should you need more than 3
colours

Sub colours()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
With Cells(i, "A")
Select Case .Value
Case "*": .Interior.ColorIndex = 3 'red
Case "**": .Interior.ColorIndex = 5 'blue
Case "***": .Interior.ColorIndex = 10 'green
End Select
End With
Next i

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)