I have the following problem.
I have three worksheets say 1, 2 and 3. I am using VLookup to return a
value in worksheet 3 if for example:

Cell A1 in worksheet 1 says Yes and
Cell A1 in worksheet 2 says No
Worksheet 3 returns Maybe in Cell A1

I have five possible answers that can be returned to worksheet 3 and I would
like to colour the cells in worksheet 3 accordingly.
Definately Yes = Dark Green
Yes = Light Green
Maybe = Light Yellow
No = Light Red
Definately No = Red

How do i do this if there are only three conditional formatting iterations.

Also what do i do if I have more than 5...

Any help would be great.

Thank you

If you don't have XL2007 then you would have to use some VBA to give
you more than 3 CF colours. Bob Phillips has a free add-in, CFPlus,
which will give you up to 30 colours - it's available he

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

Hope this helps.

Pete

On Oct 9, 2:21 pm, Rebecca wrote:
I have the following problem.
I have three worksheets say 1, 2 and 3. I am using VLookup to return a
value in worksheet 3 if for example:

Cell A1 in worksheet 1 says Yes and
Cell A1 in worksheet 2 says No
Worksheet 3 returns Maybe in Cell A1

I have five possible answers that can be returned to worksheet 3 and I would
like to colour the cells in worksheet 3 accordingly.
Definately Yes = Dark Green
Yes = Light Green
Maybe = Light Yellow
No = Light Red
Definately No = Red

How do i do this if there are only three conditional formatting iterations.

Also what do i do if I have more than 5...

Any help would be great.

Thank you

Rebecca,

My choice of colours may not be to your taste but easily changed. Right
click the sheet tab, view code and paste this in. This works for A1 but is
easily changeable to another cell or a range of cells:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
Select Case Target.Value
Case "Definately Yes"
icolour = 10
Case "Yes"
icolour = 4
Case "Maybe"
icolour = 6
Case "No"
icolour = 22
Case "Definately No"
icolour = 3
End Select
Target.Interior.ColorIndex = icolour
End If
End Sub

Mike

"Rebecca" wrote:

I have the following problem.
I have three worksheets say 1, 2 and 3. I am using VLookup to return a
value in worksheet 3 if for example:

Cell A1 in worksheet 1 says Yes and
Cell A1 in worksheet 2 says No
Worksheet 3 returns Maybe in Cell A1

I have five possible answers that can be returned to worksheet 3 and I would
like to colour the cells in worksheet 3 accordingly.
Definately Yes = Dark Green
Yes = Light Green
Maybe = Light Yellow
No = Light Red
Definately No = Red

How do i do this if there are only three conditional formatting iterations.

Also what do i do if I have more than 5...

Any help would be great.

Thank you

Thanks Mike
What if the cell I want to color is not physically the word "Yes" but a
vlookup formula which only returns "Yes"?
Rebecca

"Mike H" wrote:

Rebecca,

My choice of colours may not be to your taste but easily changed. Right
click the sheet tab, view code and paste this in. This works for A1 but is
easily changeable to another cell or a range of cells:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
Select Case Target.Value
Case "Definately Yes"
icolour = 10
Case "Yes"
icolour = 4
Case "Maybe"
icolour = 6
Case "No"
icolour = 22
Case "Definately No"
icolour = 3
End Select
Target.Interior.ColorIndex = icolour
End If
End Sub

Mike

"Rebecca" wrote:

I have the following problem.
I have three worksheets say 1, 2 and 3. I am using VLookup to return a
value in worksheet 3 if for example:

Cell A1 in worksheet 1 says Yes and
Cell A1 in worksheet 2 says No
Worksheet 3 returns Maybe in Cell A1

I have five possible answers that can be returned to worksheet 3 and I would
like to colour the cells in worksheet 3 accordingly.
Definately Yes = Dark Green
Yes = Light Green
Maybe = Light Yellow
No = Light Red
Definately No = Red

How do i do this if there are only three conditional formatting iterations.

Also what do i do if I have more than 5...

Any help would be great.

Thank you

Use Bob's add-in


--


Regards,


Peo Sjoblom



"Rebecca" wrote in message
...
Thanks Mike
What if the cell I want to color is not physically the word "Yes" but a
vlookup formula which only returns "Yes"?
Rebecca

"Mike H" wrote:

Rebecca,

My choice of colours may not be to your taste but easily changed. Right
click the sheet tab, view code and paste this in. This works for A1 but
is
easily changeable to another cell or a range of cells:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
Select Case Target.Value
Case "Definately Yes"
icolour = 10
Case "Yes"
icolour = 4
Case "Maybe"
icolour = 6
Case "No"
icolour = 22
Case "Definately No"
icolour = 3
End Select
Target.Interior.ColorIndex = icolour
End If
End Sub

Mike

"Rebecca" wrote:

I have the following problem.
I have three worksheets say 1, 2 and 3. I am using VLookup to return a
value in worksheet 3 if for example:

Cell A1 in worksheet 1 says Yes and
Cell A1 in worksheet 2 says No
Worksheet 3 returns Maybe in Cell A1

I have five possible answers that can be returned to worksheet 3 and I
would
like to colour the cells in worksheet 3 accordingly.
Definately Yes = Dark Green
Yes = Light Green
Maybe = Light Yellow
No = Light Red
Definately No = Red

How do i do this if there are only three conditional formatting
iterations.

Also what do i do if I have more than 5...

Any help would be great.

Thank you