Conditional Formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Use Bob's add-in


--


Regards,


Peo Sjoblom



Rebecca said:
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
Click to expand...
 
Back
Top