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

--
Message posted from http://www.ExcelForum.com

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



--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I would like to conditionally format two and sometimes three areas of
cells on the same worksheet, however all of these areas need more
than
three conditions and therefore has to be done through VBA. However I
am not very advanced at writting VBA code and would like some help
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 to
light blue,
when N is typed in I would like the background colour to change to
dark 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 to
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 to
light green,
when N is typed in I would like the background colour to change to
dark 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
would like it to ingore them so the background and colours can be
changed manually or left blank


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

Thanks in advance
Jennie


---
Message posted from http://www.ExcelForum.com/

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

--
Regards
Frank Kabel
Frankfurt, Germany

Frank Kabel wrote:
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




Hi,

I would like to conditionally format two and sometimes three areas
of
cells on the same worksheet, however all of these areas need more
than three conditions and therefore has to be done through VBA.
However I am not very advanced at writting VBA code and would like
some help 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 to
light blue,
when N is typed in I would like the background colour to change to
dark 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 to
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 to
light green,
when N is typed in I would like the background colour to change to
dark 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
would like it to ingore them so the background and colours can be
changed manually or left blank


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

Thanks in advance
Jennie


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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


--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

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

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

Thanks
Jennie


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/

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

"jennie " wrote in message
...
Hi,

I would like to conditionally format two and sometimes three areas of
cells on the same worksheet, however all of these areas need more than
three conditions and therefore has to be done through VBA. However I
am not very advanced at writting VBA code and would like some help
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 to
light blue,
when N is typed in I would like the background colour to change to dark
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 to
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 to
light green,
when N is typed in I would like the background colour to change to dark
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 would
like it to ingore them so the background and colours can be changed
manually or left blank


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

Thanks in advance
Jennie


---
Message posted from http://www.ExcelForum.com/