Hi,

Is it possible to introduce more than 3 conditions (via conditional
formatting) onto a single spreadsheet?

I have a spreadsheet with 9 columns - the first is for name, then 1st
choice, 2nd choice etc etc...up to 8th choice. There are 8 different things
that could appear in each cell on the same row - and I would like the cell to
change colour automatically according to what appears in it. This would
obviously require 8 conditions - but I seem limited to 3.

Is there a way to increase the number of permissible conditions, or can
anyone think of a way I can achieve this without conditions?

Thanks,

Ian

This can be accomplished by using the worksheet change event. The following
macro monitors changes in A1:A20 and set the background colour:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
With Selection.Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Application.EnableEvents = True
End Sub

The sample macro only works with integers and assigns colours based upon the
integers. You could modify it to operate in a more general way.
__________________Have a good day!
Gary's Student


"Ian.UK" wrote:

Hi,

Is it possible to introduce more than 3 conditions (via conditional
formatting) onto a single spreadsheet?

I have a spreadsheet with 9 columns - the first is for name, then 1st
choice, 2nd choice etc etc...up to 8th choice. There are 8 different things
that could appear in each cell on the same row - and I would like the cell to
change colour automatically according to what appears in it. This would
obviously require 8 conditions - but I seem limited to 3.

Is there a way to increase the number of permissible conditions, or can
anyone think of a way I can achieve this without conditions?

Thanks,

Ian

Hi,

Thanks for the feedback - going to need a little bit more help with this
unfortunately, as macros are not my forte!

What part of this macro actually changes the colour of the cell?

And when you say integers, would this this macro actually work with letters
i.e. if I wanted to a cell containing the letter P to be red?

Many thanks,

Ian

"Gary''s Student" wrote:

This can be accomplished by using the worksheet change event. The following
macro monitors changes in A1:A20 and set the background colour:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
With Selection.Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Application.EnableEvents = True
End Sub

The sample macro only works with integers and assigns colours based upon the
integers. You could modify it to operate in a more general way.
__________________Have a good day!
Gary's Student


"Ian.UK" wrote:

Hi,

Is it possible to introduce more than 3 conditions (via conditional
formatting) onto a single spreadsheet?

I have a spreadsheet with 9 columns - the first is for name, then 1st
choice, 2nd choice etc etc...up to 8th choice. There are 8 different things
that could appear in each cell on the same row - and I would like the cell to
change colour automatically according to what appears in it. This would
obviously require 8 conditions - but I seem limited to 3.

Is there a way to increase the number of permissible conditions, or can
anyone think of a way I can achieve this without conditions?

Thanks,

Ian

See:

http://www.xldynamic.com/source/xld.....Download.html

Regards
Rowan

Ian.UK wrote:
Hi,

Thanks for the feedback - going to need a little bit more help with this
unfortunately, as macros are not my forte!

What part of this macro actually changes the colour of the cell?

And when you say integers, would this this macro actually work with letters
i.e. if I wanted to a cell containing the letter P to be red?

Many thanks,

Ian

"Gary''s Student" wrote:


This can be accomplished by using the worksheet change event. The following
macro monitors changes in A1:A20 and set the background colour:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
With Selection.Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Application.EnableEvents = True
End Sub

The sample macro only works with integers and assigns colours based upon the
integers. You could modify it to operate in a more general way.
__________________Have a good day!
Gary's Student


"Ian.UK" wrote:


Hi,

Is it possible to introduce more than 3 conditions (via conditional
formatting) onto a single spreadsheet?

I have a spreadsheet with 9 columns - the first is for name, then 1st
choice, 2nd choice etc etc...up to 8th choice. There are 8 different things
that could appear in each cell on the same row - and I would like the cell to
change colour automatically according to what appears in it. This would
obviously require 8 conditions - but I seem limited to 3.

Is there a way to increase the number of permissible conditions, or can
anyone think of a way I can achieve this without conditions?

Thanks,

Ian

Then a small update:

Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As String
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
If i = "p" Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Application.EnableEvents = True
End Sub

Here we are only looking for the user to enter p. The colorindex=3 sets the
background colour to red. If you want other letters to trigger other
colours, then replicate the IF structure with a different letter and a
different colorindex.


With regard to entering and using this type of macro, see:


http://www.mvps.org/dmcritchie/excel/event.htm
--
Gary''s Student


"Ian.UK" wrote:

Hi,

Thanks for the feedback - going to need a little bit more help with this
unfortunately, as macros are not my forte!

What part of this macro actually changes the colour of the cell?

And when you say integers, would this this macro actually work with letters
i.e. if I wanted to a cell containing the letter P to be red?

Many thanks,

Ian

"Gary''s Student" wrote:

This can be accomplished by using the worksheet change event. The following
macro monitors changes in A1:A20 and set the background colour:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
With Selection.Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Application.EnableEvents = True
End Sub

The sample macro only works with integers and assigns colours based upon the
integers. You could modify it to operate in a more general way.
__________________Have a good day!
Gary's Student


"Ian.UK" wrote:

Hi,

Is it possible to introduce more than 3 conditions (via conditional
formatting) onto a single spreadsheet?

I have a spreadsheet with 9 columns - the first is for name, then 1st
choice, 2nd choice etc etc...up to 8th choice. There are 8 different things
that could appear in each cell on the same row - and I would like the cell to
change colour automatically according to what appears in it. This would
obviously require 8 conditions - but I seem limited to 3.

Is there a way to increase the number of permissible conditions, or can
anyone think of a way I can achieve this without conditions?

Thanks,

Ian

Hi again,

Ok, well this seems to be working better, thanks very much so far!

A couple of further problems though I am afraid.

Firstly, when I apply this code a cell in the range does go red when I type
in a 'P'. However if I delete the 'P' the cell stays red even though it is
empty. I'd like it to go to white again, but am unsure about what code would
allow me to do this?

Secondly, if I select a number of cells from the range at the same time and
delete the contents, I get run time error '13' (Type Mismatch). When
debugged, this highlights the line of code "i = Selection.Value" as the
problem.

Help with either of these problems would be really appreciated.

Many thanks again,

Ian

"Gary''s Student" wrote:

Then a small update:

Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As String
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
If i = "p" Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Application.EnableEvents = True
End Sub

Here we are only looking for the user to enter p. The colorindex=3 sets the
background colour to red. If you want other letters to trigger other
colours, then replicate the IF structure with a different letter and a
different colorindex.


With regard to entering and using this type of macro, see:


http://www.mvps.org/dmcritchie/excel/event.htm
--
Gary''s Student


"Ian.UK" wrote:

Hi,

Thanks for the feedback - going to need a little bit more help with this
unfortunately, as macros are not my forte!

What part of this macro actually changes the colour of the cell?

And when you say integers, would this this macro actually work with letters
i.e. if I wanted to a cell containing the letter P to be red?

Many thanks,

Ian

"Gary''s Student" wrote:

This can be accomplished by using the worksheet change event. The following
macro monitors changes in A1:A20 and set the background colour:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
With Selection.Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Application.EnableEvents = True
End Sub

The sample macro only works with integers and assigns colours based upon the
integers. You could modify it to operate in a more general way.
__________________Have a good day!
Gary's Student


"Ian.UK" wrote:

Hi,

Is it possible to introduce more than 3 conditions (via conditional
formatting) onto a single spreadsheet?

I have a spreadsheet with 9 columns - the first is for name, then 1st
choice, 2nd choice etc etc...up to 8th choice. There are 8 different things
that could appear in each cell on the same row - and I would like the cell to
change colour automatically according to what appears in it. This would
obviously require 8 conditions - but I seem limited to 3.

Is there a way to increase the number of permissible conditions, or can
anyone think of a way I can achieve this without conditions?

Thanks,

Ian

Try this adaption:

Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo ErrorHandler
Dim cell As Range
Application.EnableEvents = False
If Not Intersect(Range("A1:A10"), Target) Is Nothing Then
For Each cell In Intersect(Range("A1:A10"), Target)
If cell.Value = "p" Then
cell.Interior.ColorIndex = 3
Else
cell.Interior.ColorIndex = xlNone
End If
Next cell
End If

ErrorHandler:
Application.EnableEvents = True
End Sub

Hope this helps
Rowan

Ian.UK wrote:
Hi again,

Ok, well this seems to be working better, thanks very much so far!

A couple of further problems though I am afraid.

Firstly, when I apply this code a cell in the range does go red when I type
in a 'P'. However if I delete the 'P' the cell stays red even though it is
empty. I'd like it to go to white again, but am unsure about what code would
allow me to do this?

Secondly, if I select a number of cells from the range at the same time and
delete the contents, I get run time error '13' (Type Mismatch). When
debugged, this highlights the line of code "i = Selection.Value" as the
problem.

Help with either of these problems would be really appreciated.

Many thanks again,

Ian

"Gary''s Student" wrote:


Then a small update:

Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As String
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
If i = "p" Then
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Application.EnableEvents = True
End Sub

Here we are only looking for the user to enter p. The colorindex=3 sets the
background colour to red. If you want other letters to trigger other
colours, then replicate the IF structure with a different letter and a
different colorindex.


With regard to entering and using this type of macro, see:


http://www.mvps.org/dmcritchie/excel/event.htm
--
Gary''s Student


"Ian.UK" wrote:


Hi,

Thanks for the feedback - going to need a little bit more help with this
unfortunately, as macros are not my forte!

What part of this macro actually changes the colour of the cell?

And when you say integers, would this this macro actually work with letters
i.e. if I wanted to a cell containing the letter P to be red?

Many thanks,

Ian

"Gary''s Student" wrote:


This can be accomplished by using the worksheet change event. The following
macro monitors changes in A1:A20 and set the background colour:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A10"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Select
i = Selection.Value
With Selection.Interior
.ColorIndex = i
.Pattern = xlSolid
End With
Application.EnableEvents = True
End Sub

The sample macro only works with integers and assigns colours based upon the
integers. You could modify it to operate in a more general way.
__________________Have a good day!
Gary's Student


"Ian.UK" wrote:


Hi,

Is it possible to introduce more than 3 conditions (via conditional
formatting) onto a single spreadsheet?

I have a spreadsheet with 9 columns - the first is for name, then 1st
choice, 2nd choice etc etc...up to 8th choice. There are 8 different things
that could appear in each cell on the same row - and I would like the cell to
change colour automatically according to what appears in it. This would
obviously require 8 conditions - but I seem limited to 3.

Is there a way to increase the number of permissible conditions, or can
anyone think of a way I can achieve this without conditions?

Thanks,

Ian