I'm trying to have an autoshape turn either red or green dependent upon the
value in two other separate cells.

Example: if A1 0 or if B1 0, the make the autoshape green
otherwise, make the autoshape red.

Is there any VBA code out that that I could paste to do this?

Thanks for any help you can give.
--
Dennis

Sub Macro1()
ActiveSheet.Shapes("Oval 1").Select
If Range("A1").Value 0 Or Range("B1").Value 0 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If
End Sub
--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

I'm trying to have an autoshape turn either red or green dependent upon the
value in two other separate cells.

Example: if A1 0 or if B1 0, the make the autoshape green
otherwise, make the autoshape red.

Is there any VBA code out that that I could paste to do this?

Thanks for any help you can give.
--
Dennis

Thanks for getting back to me.

I've tried the code, but it's not yet working. I'm not a VBA guy, but I
pasted the code into (General) and it's named Macro1. I've also assigned
the macro to the autoshape. I'm using A1 and B1 from the same sheet as the
autoshape, and the values in those cells are entered manually by me (not
formulas).

After pasting the code, I just 'saved' it. There must be something very
small that I'm missing, but I can't think of it. Is there anything else
special that I need to do to the autoshape?

Thanks again.
--
Dennis


"Gary''s Student" wrote:

Sub Macro1()
ActiveSheet.Shapes("Oval 1").Select
If Range("A1").Value 0 Or Range("B1").Value 0 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If
End Sub
--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

I'm trying to have an autoshape turn either red or green dependent upon the
value in two other separate cells.

Example: if A1 0 or if B1 0, the make the autoshape green
otherwise, make the autoshape red.

Is there any VBA code out that that I could paste to do this?

Thanks for any help you can give.
--
Dennis

We are making progress:

A. Adaptation
1. The coding uses a shape called "Oval 1". Make sure you update to match
the name of your shape.

B. Installation
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window


C. Usage
1. Don't assign the macro to the shape, it's designed to run manually:
2. ALT-F8
pick the macro
touch Run

If you want the macro to run automatically whenever A1 or B1 are manually
changed, update this post.
--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

Thanks for getting back to me.

I've tried the code, but it's not yet working. I'm not a VBA guy, but I
pasted the code into (General) and it's named Macro1. I've also assigned
the macro to the autoshape. I'm using A1 and B1 from the same sheet as the
autoshape, and the values in those cells are entered manually by me (not
formulas).

After pasting the code, I just 'saved' it. There must be something very
small that I'm missing, but I can't think of it. Is there anything else
special that I need to do to the autoshape?

Thanks again.
--
Dennis


"Gary''s Student" wrote:

Sub Macro1()
ActiveSheet.Shapes("Oval 1").Select
If Range("A1").Value 0 Or Range("B1").Value 0 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If
End Sub
--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

I'm trying to have an autoshape turn either red or green dependent upon the
value in two other separate cells.

Example: if A1 0 or if B1 0, the make the autoshape green
otherwise, make the autoshape red.

Is there any VBA code out that that I could paste to do this?

Thanks for any help you can give.
--
Dennis

Hi. Yes, I'm seeing progress. I THINK I have it to the point where if I
manually run the macro, it works. You hit the nail on the head with your
last sentence in that I DO want it to update automatically whenever A1 or B2
are changed.

Is that doable?

Thanks much!


--
Dennis


"Gary''s Student" wrote:

We are making progress:

A. Adaptation
1. The coding uses a shape called "Oval 1". Make sure you update to match
the name of your shape.

B. Installation
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window


C. Usage
1. Don't assign the macro to the shape, it's designed to run manually:
2. ALT-F8
pick the macro
touch Run

If you want the macro to run automatically whenever A1 or B1 are manually
changed, update this post.
--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

Thanks for getting back to me.

I've tried the code, but it's not yet working. I'm not a VBA guy, but I
pasted the code into (General) and it's named Macro1. I've also assigned
the macro to the autoshape. I'm using A1 and B1 from the same sheet as the
autoshape, and the values in those cells are entered manually by me (not
formulas).

After pasting the code, I just 'saved' it. There must be something very
small that I'm missing, but I can't think of it. Is there anything else
special that I need to do to the autoshape?

Thanks again.
--
Dennis


"Gary''s Student" wrote:

Sub Macro1()
ActiveSheet.Shapes("Oval 1").Select
If Range("A1").Value 0 Or Range("B1").Value 0 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If
End Sub
--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

I'm trying to have an autoshape turn either red or green dependent upon the
value in two other separate cells.

Example: if A1 0 or if B1 0, the make the autoshape green
otherwise, make the autoshape red.

Is there any VBA code out that that I could paste to do this?

Thanks for any help you can give.
--
Dennis

1. delete the old macro
2. install the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:B1")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Shapes("Oval 1").Select
If Range("A1").Value 0 Or Range("B1").Value 0 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If
ActiveCell.Select
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window


To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

Hi. Yes, I'm seeing progress. I THINK I have it to the point where if I
manually run the macro, it works. You hit the nail on the head with your
last sentence in that I DO want it to update automatically whenever A1 or B2
are changed.

Is that doable?

Thanks much!


--
Dennis


"Gary''s Student" wrote:

We are making progress:

A. Adaptation
1. The coding uses a shape called "Oval 1". Make sure you update to match
the name of your shape.

B. Installation
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window


C. Usage
1. Don't assign the macro to the shape, it's designed to run manually:
2. ALT-F8
pick the macro
touch Run

If you want the macro to run automatically whenever A1 or B1 are manually
changed, update this post.
--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

Thanks for getting back to me.

I've tried the code, but it's not yet working. I'm not a VBA guy, but I
pasted the code into (General) and it's named Macro1. I've also assigned
the macro to the autoshape. I'm using A1 and B1 from the same sheet as the
autoshape, and the values in those cells are entered manually by me (not
formulas).

After pasting the code, I just 'saved' it. There must be something very
small that I'm missing, but I can't think of it. Is there anything else
special that I need to do to the autoshape?

Thanks again.
--
Dennis


"Gary''s Student" wrote:

Sub Macro1()
ActiveSheet.Shapes("Oval 1").Select
If Range("A1").Value 0 Or Range("B1").Value 0 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If
End Sub
--
Gary''s Student - gsnu200786


"Dennis Collins" wrote:

I'm trying to have an autoshape turn either red or green dependent upon the
value in two other separate cells.

Example: if A1 0 or if B1 0, the make the autoshape green
otherwise, make the autoshape red.

Is there any VBA code out that that I could paste to do this?

Thanks for any help you can give.
--
Dennis