Multiple Conditional Formatting Using VBA

[jennie]

Hi,

I would like to conditionally format two and sometimes three areas o
cells on the same worksheet, however all of these areas need more tha
three conditions and therefore has to be done through VBA. However
am not very advanced at writting VBA code and would like some hel
please.

Here is a mini version of my problem:

In cells A1:C10,
when D is typed in I would like the background colour to change t
light blue,
when N is typed in I would like the background colour to change to dar
blue and the font colour to white
DC background colour to light purple
NC background colour to dark purple and font to white

Also
In cells A11:C20,
when D is typed in I would like the background colour to change t
gold,
DC background colour to light purple
NC background colour to dark purple and font to white
S background colour to pink
L background colour to light grey

and on some worksheets:
In cells A21:C30,
when D is typed in I would like the background colour to change t
light green,
when N is typed in I would like the background colour to change to dar
green and the font colour to white
DC background colour to light purple
NC background colour to dark purple and font to white

In all cases when anything else is typed in, including numbers, I woul
like it to ingore them so the background and colours can be change
manually or left blank


I think this has to be a change event individual to each sheet, as eac
sheet requires different colour combinations, but that is about as fa
I know.

Thanks in advance
Jenni

 

[Frank Kabel]

Hi

You need to process the worksheet_change event and apply your format
based on the cell values).
The following will color the entry in cell A1:C10 based on its value
(put this code in your worksheet module):
You have to adapt the color indexes for each value (have a look at for
color codes

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:C10")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case "D": .interior.ColorIndex = 3
Case "N":
.interior.ColorIndex = 4
.Font.ColorIndex = 3
Case "DC": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

 

[Frank Kabel]

Hi
and this is the site for the color information
http://www.mvps.org/dmcritchie/excel/colors.htm

 

[jennie]

Hi,

I copied the code you wrote into the worksheet code box (which you ge
by right clicking on the worksheet tab and selecting view code) - i
this the correct place for the code?
But when I then typed into the cells nothing happened, they did no
change colour.

Also how do you go about setting it up for the muliple areas and how d
you change the font colour.

Thanks
Jenni

 

[Frank Kabel]

Hi
the following should work (and yes this is the right place you put the
code into)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:C10")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case "D"
.Interior.ColorIndex = 3
Case "N"
.Interior.ColorIndex = 4
.Font.ColorIndex = 3
Case "DC"
.Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


for changing the font color use
.. font.colorindex = n
within the select case statement

 

[jennie]

Hi,

Unfortunatly it is still not working, is it something to do with it
running on Excel 2000?

This is the same problem I have had with sample code to do the same
thing from the internet, the code doesn't throw up any errors it just
doesn't seem to work.

Any ideas?

Thanks
Jennie

 

[Bob Flanagan]

Jennie, take a look at the Conditional Format Assistant at

http://www.add-ins.com/conditional_format_assistant.htm

It will do what you want.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Frank Kabel]

Hi
if you like email me your file and I'll have a look at it
(frank[dot]kabel[at]freenet[dot]de)