Conditional Formatting

10 views
Skip to first unread message

Dave

unread,
Feb 7, 2002, 2:54:05 AM2/7/02
to
Guys,

I'm wanting to change the colour of a cell based on it's current
value, but unsure of how to proceed with VB. Conditional formatting
only allows three options.

This does not need to be "Range Based" as I would like this to occur
in any cell in any sheet of the current workbook when a cell is
modified. ie) event based, for example if I was to type in "A" then
the cell that I have just modified would turn blue, "B", Green etc...
for nine different characters - nine different colours....

Your help would be much appreciated.

Dave.

J.E. McGimpsey

unread,
Feb 7, 2002, 6:03:09 AM2/7/02
to
One way:

You'll need a Workbook SheetChange event macro (right click on the
workbook title bar, choose View Code, and paste the following into the
ThisWorkbook module). Change the values in charArr and colorArr as you
like:


Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim cell As Range
Dim charArr As Variant
Dim colorArr As Variant
Dim i As Long

'Substitute your preferred characters here:
charArr = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")

'Substitute your preferred colorindexes and order here:
colorArr = Array(3, 4, 5, 6, 7, 8, 9, 10, 11)

For Each cell In Target
With cell
.Interior.ColorIndex = xlColorIndexNone
For i = 0 To UBound(charArr)
If .Value = charArr(i) Then
.Interior.ColorIndex = colorArr(i)
Exit For
End If
Next i
End With
Next cell
End Sub


In article <526e114e.02020...@posting.google.com>, Dave
<promet...@excite.com.au> wrote:

--
Email address ROT13 encoded. Decode for real address.

David McRitchie

unread,
Feb 7, 2002, 6:35:29 PM2/7/02
to
Hi Dave, and J.E.,
Those array statements look neater than Case statements
on my site; however, I think
some choices of colors might be in order see
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm

"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote in message news:070220020403090081%ar...@zptvzcfrl.pbz...

J.E. McGimpsey

unread,
Feb 7, 2002, 6:57:21 PM2/7/02
to
Couldn't agree with you more, David - I are not a designer, by any
stretch of the imagination...

The sequential run of colors I used is garish to my eye, but not
everyone shares my prediliction for purple.


In article <uDwuMBDsBHA.2684@tkmsftngp05>, David McRitchie
<dmcri...@msn.com> wrote:

> Hi Dave, and J.E.,
> Those array statements look neater than Case statements
> on my site; however, I think
> some choices of colors might be in order see
> Color Palette -- the 56 Excel Colors
> http://www.mvps.org/dmcritchie/excel/colors.htm
>

--

Dave

unread,
Feb 11, 2002, 10:12:54 PM2/11/02
to
J.E.

Thanks very much for this - exactly what I wanted. Worked a treat.

Dave.

"J.E. McGimpsey" <ar...@zptvzcfrl.pbz> wrote in message news:<070220020403090081%ar...@zptvzcfrl.pbz>...

Reply all
Reply to author
Forward
0 new messages