Hi,

I have a column where are values 3,2,1 and I want to make a "traffic
light" indivator out of them. 3 is green, 2 is yellow and 1 is red. If
the cell is blank, it means yellow.
I made 3 ovals on the sheet.
The logical solution would be: (if 3 then green, (if 1 then red,), else
yellow)
But I can't code it.
The light indicator should be next to the cell that has the value.

Juuljus,
Would Conditional Formatting be sufficient ?

NickHK

"Juuljus" wrote in message
oups.com...
Hi,

I have a column where are values 3,2,1 and I want to make a "traffic
light" indivator out of them. 3 is green, 2 is yellow and 1 is red. If
the cell is blank, it means yellow.
I made 3 ovals on the sheet.
The logical solution would be: (if 3 then green, (if 1 then red,), else
yellow)
But I can't code it.
The light indicator should be next to the cell that has the value.

Show me what you have in mind.

That would be cells, not ovals, and just test the cell value within the
conditional formatting. Check CF in help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Juuljus" wrote in message
oups.com...
Show me what you have in mind.

I knew that. Colorful cell would look ugly. I need ovals.

OK, what will change the string "yellow" to shape named "yellow"?

Dim rlColor As String
With Worksheets("Temp").Range("AM10:AM40")
If rlColor = "yellow" Then

End If
If rlColor = "red" Then

Else

End If
End With

If you can figure out a way to show shapes that way then yes.

Hi Juuljus,
This isn't exactly what you want but it might give you some ideas.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address() < "$A$1" Then
Exit Sub
End If
Dim Sstop As Shape, GetReady As Shape, Go As Shape
Set Sstop = ActiveSheet.Shapes("Stop")
Set GetReady = ActiveSheet.Shapes("Get Ready")
Set Go = ActiveSheet.Shapes("Go")
Select Case Range("A1")
Case 1
Sstop.Fill.ForeColor.SchemeColor = 10
GetReady.Fill.ForeColor.SchemeColor = 9
Go.Fill.ForeColor.SchemeColor = 9
Case 2
Sstop.Fill.ForeColor.SchemeColor = 9
GetReady.Fill.ForeColor.SchemeColor = 13
Go.Fill.ForeColor.SchemeColor = 9
Case 3
Sstop.Fill.ForeColor.SchemeColor = 9
GetReady.Fill.ForeColor.SchemeColor = 9
Go.Fill.ForeColor.SchemeColor = 11
End Select
End Sub

The worksheet has 3 circular autoshapes that have been named "Stop",
"Get Ready" and "Go"
The code resides in the ThisWorkbook Module and is automatically run
when A1's value changes (1=Stop is red, 2=Get Ready is yellow, 3=Go is
green)

I had to use Sstop as a variable name because Stop is not allowed.

Ken Johnson

Hi Juuljus,
If you want the traffic lights to be operated according to the value in
the selected cell in column A then similar code can go into the
Workbook_SheetSelectionChange Sub of the ThisWorkbook Module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
If Target.Column < 1 Then
Exit Sub
End If
Dim Sstop As Shape, GetReady As Shape, Go As Shape
Set Sstop = ActiveSheet.Shapes("Stop")
Set GetReady = ActiveSheet.Shapes("Get Ready")
Set Go = ActiveSheet.Shapes("Go")
Select Case Target
Case 1
Sstop.Fill.ForeColor.SchemeColor = 10
GetReady.Fill.ForeColor.SchemeColor = 9
Go.Fill.ForeColor.SchemeColor = 9
Case 2
Sstop.Fill.ForeColor.SchemeColor = 9
GetReady.Fill.ForeColor.SchemeColor = 13
Go.Fill.ForeColor.SchemeColor = 9
Case 3
Sstop.Fill.ForeColor.SchemeColor = 9
GetReady.Fill.ForeColor.SchemeColor = 9
Go.Fill.ForeColor.SchemeColor = 11
End Select
End Sub
Only the first and eigth code lines have been changed.
If you have a sequence of 1's, 2's and 3's down column A the traffic
lights will change is you change the cell selected in column A

Ken Johnson

Hi Juuljus,
Just noticed you want the traffic lights to move to the selection
position.Try this for 1's, 2's, 3's and blanks in column A. I've
changed the Select Case to deal with values other than 1,2,or 3 to give
yellow light.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
If Target.Column < 1 Then
Exit Sub
End If
Dim Sstop As Shape, GetReady As Shape, Go As Shape
Set Sstop = ActiveSheet.Shapes("Stop")
Set GetReady = ActiveSheet.Shapes("Get Ready")
Set Go = ActiveSheet.Shapes("Go")
Sstop.Top = Target.Top
GetReady.Top = Target.Top + Sstop.Height
Go.Top = Target.Top + GetReady.Height + Sstop.Height
Select Case Target
Case 1
Sstop.Fill.ForeColor.SchemeColor = 10
GetReady.Fill.ForeColor.SchemeColor = 9
Go.Fill.ForeColor.SchemeColor = 9
Case 2
Sstop.Fill.ForeColor.SchemeColor = 9
GetReady.Fill.ForeColor.SchemeColor = 13
Go.Fill.ForeColor.SchemeColor = 9
Case 3
Sstop.Fill.ForeColor.SchemeColor = 9
GetReady.Fill.ForeColor.SchemeColor = 9
Go.Fill.ForeColor.SchemeColor = 11
Case Else
Sstop.Fill.ForeColor.SchemeColor = 9
GetReady.Fill.ForeColor.SchemeColor = 13
Go.Fill.ForeColor.SchemeColor = 9
End Select
End Sub

I will mail you a copy of the worksheet. Make sure security setting is
medium so that macro will work.

Ken Johnson

Use the wingdings font on the traffic light cell.
Cell contains the l character (lower case L)
Condition formatting based on cell to left for various colours.

Cheers
Andy

Juuljus wrote:
Hi,

I have a column where are values 3,2,1 and I want to make a "traffic
light" indivator out of them. 3 is green, 2 is yellow and 1 is red. If
the cell is blank, it means yellow.
I made 3 ovals on the sheet.
The logical solution would be: (if 3 then green, (if 1 then red,), else
yellow)
But I can't code it.
The light indicator should be next to the cell that has the value.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Good idea Andy.

I have never used the Conditional formatting option.
How can I make the cell I want to color (the one with the l) decide
which color by the cell that has "yello", "green" and "red" strings?

Juuljus

Select the traffic lights, but where the cells with yello, green, red let's
say start at A1

Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =A1="red"
Click the Format button
Select the Font Tab
Select red from the Color dropdown
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =A1="yello"
Click the Format button
Select the Font Tab
Select yellow from the Color dropdown
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =A1="green"
Click the Format button
Select the Font Tab
Select green from the Color dropdown
OK
OK

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Juuljus" wrote in message
oups.com...
Good idea Andy.

I have never used the Conditional formatting option.
How can I make the cell I want to color (the one with the l) decide
which color by the cell that has "yello", "green" and "red" strings?

Juuljus

Thanks Bob. If you default the cell color to Green you can get away with
only 2 conditions.

Cheers
Andy

Bob Phillips wrote:
Select the traffic lights, but where the cells with yello, green, red let's
say start at A1

Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =A1="red"
Click the Format button
Select the Font Tab
Select red from the Color dropdown
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =A1="yello"
Click the Format button
Select the Font Tab
Select yellow from the Color dropdown
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =A1="green"
Click the Format button
Select the Font Tab
Select green from the Color dropdown
OK
OK


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy,

I've got it filed away in a drawer for such occasions :-))

I only ever bother with the default colour when I want 4.

Bob


"Andy Pope" wrote in message
...
Thanks Bob. If you default the cell color to Green you can get away with
only 2 conditions.

Cheers
Andy

Bob Phillips wrote:
Select the traffic lights, but where the cells with yello, green, red
let's
say start at A1

Menu FormatConditional Formatting
Change Condition 1 to Formula Is
Add a formula of =A1="red"
Click the Format button
Select the Font Tab
Select red from the Color dropdown
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =A1="yello"
Click the Format button
Select the Font Tab
Select yellow from the Color dropdown
OK
OK

Second condition

Click Add
Change Condition 2 to Formula Is
Add a formula of =A1="green"
Click the Format button
Select the Font Tab
Select green from the Color dropdown
OK
OK


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info