autoshape color conditional formating

[Dennis Collins]

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.

 

[Gary''s Student]

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

 

[Dennis Collins]

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.

 

[Gary''s Student]

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.

 

[Dennis Collins]

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!

 

[Gary''s Student]

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

 

[Dennis Collins]

That works! Thanks.

Now for the final topping. I notice that the value in A1 or B1 must be
manually entered. My original idea was to have those cells reference cells
on another sheet.

In short, I would have cells A1 and B1 hidden on sheet one (just showing the
autoshape). On sheet 2 I would have values that sum up and are referenced by
the A1 and B1 on sheet one.

Any way to do that?

(I really appreciate your help on this!!)

 

[Ken Johnson]

That works! Thanks.

Now for the final topping. I notice that the value in A1 or B1 must be
manually entered. My original idea was to have those cells reference cells
on another sheet.

In short, I would have cells A1 and B1 hidden on sheet one (just showing the
autoshape). On sheet 2 I would have values that sum up and are referenced by
the A1 and B1 on sheet one.

Any way to do that?

(I really appreciate your help on this!!)

Use the Worksheet_Calculate event when change is a calculated
change...

Private Sub Worksheet_Calculate()
If Range("A1").Value > 0 Or Range("B1").Value > 0 Then
Me.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 17
Else
Me.Shapes("Oval 1").Fill.ForeColor.SchemeColor = 10
End If
End Sub

Ken Johnson

 

[Dennis Collins]

Ken,

Thanks for the calculate tip. My problem now is that the cell that I need
to reference is on a different worksheet. I can't figure out how to
reference that worksheet (and get it to work!). I've tried the
....sheet!abc!A1... trick, but it still won't work.

 

[Ken Johnson]

Ken,

Thanks for the calculate tip. My problem now is that the cell that I need
to reference is on a different worksheet. I can't figure out how to
reference that worksheet (and get it to work!). I've tried the
...sheet!abc!A1... trick, but it still won't work.

Have I got this right?...
You have a sheet with a shape object (referred to as Oval 1 in the
above code) and you want its color to be determined by the values
shown in A1 and B1 on the same sheet (if either are greater than 0
then green, otherwise red). At least one of the values (A1, B1), or
maybe both, are(is) the result of a calculation, which is simply a
reference to the value in a cell (A1) on another sheet.
You have stated..
I've tried the...sheet!abc!A1... trick, but it still won't work.
"sheet!abc!A1" looks very strange.
If the name of the sheet being referred to is abc then the formula you
should be using on the sheet with the shape is =abc!A1.
If the name of the sheet is sheet!abc, which I very much doubt, then
the formula would be ='sheet!abc'!A1.
So, If what I have said is correct, then maybe the only problem is the
formula you are using to refer to that other sheet.
When referring to another sheet in a formula you can type the equals
sign, click on the tab of the referred-to sheet, click in the referred-
to cell in the referred-to sheet then press Enter.

I hope that gets it working.

Ken Johnson

 

[Dennis Collins]

Hi Ken,

Thanks for getting back to me. My issues are two-fold. I'm trying to:

A) Have an autoshape change color based on a value in a cell.
I have that working, but I'm unable to have that cell reference a
cell on another worksheet

B) Have an autoshape disappear (visible=false) based on a value in the
worksheet.
As with A above, I have that working but I'm unable to have that
cell reference a cell on another worksheet.

I've tried your suggestion of entering '=' then clicking on the cell in the
other worksheet, but it bombs out and the debug opens up. Here's a piece of
the code which is trying to 'hide' an autoshape:

Private Sub Worksheet_Calculate()
With Range("A3")
ActiveSheet.Shapes("LowRisk2").Visible = IIf(.Value > 0, False, True)
End With
End Sub

With this code, A3 is a cell on the same worksheet as the autoshape, but
needs to reference a cell from another worksheet. This works only if A3 is
on the same worksheet (and, it has to be a formula, not an entered value).

I might be in too deep for my skillset...

Thanks.

Dennis

 

[Ken Johnson]

Hi Ken,

Thanks for getting back to me. My issues are two-fold. I'm trying to:

A) Have an autoshape change color based on a value in a cell.
I have that working, but I'm unable to have that cell reference a
cell on another worksheet

B) Have an autoshape disappear (visible=false) based on a value in the
worksheet.
As with A above, I have that working but I'm unable to have that
cell reference a cell on another worksheet.

I've tried your suggestion of entering '=' then clicking on the cell in the
other worksheet, but it bombs out and the debug opens up. Here's a piece of
the code which is trying to 'hide' an autoshape:

Private Sub Worksheet_Calculate()
With Range("A3")
ActiveSheet.Shapes("LowRisk2").Visible = IIf(.Value > 0, False, True)
End With
End Sub

With this code, A3 is a cell on the same worksheet as the autoshape, but
needs to reference a cell from another worksheet. This works only if A3 is
on the same worksheet (and, it has to be a formula, not an entered value).

I might be in too deep for my skillset...

Thanks.

Dennis

Hi Dennis,
I have placed an autoshape (Oval 1) on Sheet1, pasted the code I sent
into sheet1's code module and used =Sheet2!A1 in Sheet1!A1 and =Sheet2!
B1 in Sheet1!B1. When I switch to Sheet2 and change the values in
Sheet2!A1 and Sheet2!B1, then switch back to Sheet1! I get the desired
result and no error messages.

Is it possible for you to email me a copy of the workbook or a
workbook with similar structure? (kencjohnsonATgmaildotcom replace AT
and dot with usual characters).

Ken Johnson