Conditional Format for Object

[Guest]

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

Thanks

 

[Guest]

Part A - as far as I know you cannot assign conditional formatting to an
object such as a circle, text box or square.

For part B
I'd start by going ahead and setting the cell shading of B2:B4 as yellow, it
will save you one conditional test. Then select B2:B4 and use conditional
formatting and choose the Formula Is option and enter a statement like this
as the first condition:
=A$1<10
and set the shading condition, then Add another condition, again using
Formula Is and a condition like this:
=A$1>50
and set up your conditional shading. Change the values of 10 and 50 as
appropriate for the range in question.

 

[Guest]

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis

 

[Guest]

The key to your code (which is definitely an option) for the shape is knowing
the name of the shape; Pivot Man, easy way to find that is to simply click on
the object and look at the Name Box. The Name Box is that area that normally
shows the cell address of the current cell: just above the '1' for row
numbers, and to the left of the "A" column designator.

 

[Guest]

Thank you both...very much appreciated.

The key to your code (which is definitely an option) for the shape is knowing
the name of the shape; Pivot Man, easy way to find that is to simply click on
the object and look at the Name Box. The Name Box is that area that normally
shows the cell address of the current cell: just above the '1' for row
numbers, and to the left of the "A" column designator.

 

[Guest]

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

 

[Guest]

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub

 

[Guest]

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

 

[Guest]

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
:

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

Thanks

 

[Guest]

Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
:

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

Thanks

 

[Guest]

If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
:

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

Thanks

 

[Guest]

Hello again,

the 5 shapes would be on the same page. Each refercing a different cell,
say a1 to a5.

I tried replicating the code and changing the shape name in VB but that did
not work.

any thoughts?

Thanks

If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

:

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
:

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

Thanks

 

[Guest]

Sure, we can use a special type of If Then, the Select Case to deal with
these multiple conditions. You should be able to modify it for your needs
pretty easily:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Integer ' or other type
Dim upperLimit As Integer ' or other type
Dim lowerColor As Integer
Dim upperColor As Integer
Dim defaultColor As Integer

Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case Is = "$B$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 1")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case Is = "$C$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$D$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 2")
lowerLimit = 30
upperLimit = 40
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$E$1"
Set anyShape = Worksheets("Sheet1").Shapes("AutoShape 1")
lowerLimit = 130
upperLimit = 140
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents=True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If Target.Value < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf Target.Value > upperColor Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape=Nothing
End Sub

Hello again,

the 5 shapes would be on the same page. Each refercing a different cell,
say a1 to a5.

I tried replicating the code and changing the shape name in VB but that did
not work.

any thoughts?

Thanks

If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub



:

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

:

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
:

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

Thanks

 

[Guest]

Thanks again JLatham,

Unfortunately, I am getting an Runtime error '6' overflow error when I do
this. The code is stopping at the first uppercolor statement. Again, i
really appreciate your help.

Sure, we can use a special type of If Then, the Select Case to deal with
these multiple conditions. You should be able to modify it for your needs
pretty easily:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Integer ' or other type
Dim upperLimit As Integer ' or other type
Dim lowerColor As Integer
Dim upperColor As Integer
Dim defaultColor As Integer

Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case Is = "$B$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 1")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case Is = "$C$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$D$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 2")
lowerLimit = 30
upperLimit = 40
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$E$1"
Set anyShape = Worksheets("Sheet1").Shapes("AutoShape 1")
lowerLimit = 130
upperLimit = 140
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents=True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If Target.Value < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf Target.Value > upperColor Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape=Nothing
End Sub

Hello again,

the 5 shapes would be on the same page. Each refercing a different cell,
say a1 to a5.

I tried replicating the code and changing the shape name in VB but that did
not work.

any thoughts?

Thanks

If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

:

Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub



:

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

:

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
:

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

Thanks

 

[Guest]

mea culpa,
change the three definitions of the ...Color variables to type Long instead
of Integer; as
Dim lowerColor As Long
Dim upperColor as Long
Dim defaultColor as Long

Thanks again JLatham,

Unfortunately, I am getting an Runtime error '6' overflow error when I do
this. The code is stopping at the first uppercolor statement. Again, i
really appreciate your help.

Sure, we can use a special type of If Then, the Select Case to deal with
these multiple conditions. You should be able to modify it for your needs
pretty easily:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Integer ' or other type
Dim upperLimit As Integer ' or other type
Dim lowerColor As Integer
Dim upperColor As Integer
Dim defaultColor As Integer

Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case Is = "$B$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 1")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case Is = "$C$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$D$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 2")
lowerLimit = 30
upperLimit = 40
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$E$1"
Set anyShape = Worksheets("Sheet1").Shapes("AutoShape 1")
lowerLimit = 130
upperLimit = 140
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents=True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If Target.Value < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf Target.Value > upperColor Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape=Nothing
End Sub

Hello again,

the 5 shapes would be on the same page. Each refercing a different cell,
say a1 to a5.

I tried replicating the code and changing the shape name in VB but that did
not work.

any thoughts?

Thanks



:

If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

:

Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub



:

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

:

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
:

Looking to change the format of an object...change the color of a circle to
red if less than, yellow if between two values and green if greater than a
value. I can use condiitional formatting in a cell, just wondering if

A) you can apply condiitonal formatting to an object
or B) can you apply conditional formatting to cells outside of the result
cell. ie, depending on the results in cell A1, I want to change the color in
Cells B2 to B4.

 

[Guest]

Hey, this is still not working. no longer getting errors, but colors do not
change consistently, nor does the limit seem to work.

ideally, the conditions would be

if <10 then Red
if >30 then Green
All else is Yellow

not sure if part of the problem is the limits.

I have checked the shape names and the cell references and they all seem to
be correct.
Thanks again.

mea culpa,
change the three definitions of the ...Color variables to type Long instead
of Integer; as
Dim lowerColor As Long
Dim upperColor as Long
Dim defaultColor as Long

Thanks again JLatham,

Unfortunately, I am getting an Runtime error '6' overflow error when I do
this. The code is stopping at the first uppercolor statement. Again, i
really appreciate your help.

Sure, we can use a special type of If Then, the Select Case to deal with
these multiple conditions. You should be able to modify it for your needs
pretty easily:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Integer ' or other type
Dim upperLimit As Integer ' or other type
Dim lowerColor As Integer
Dim upperColor As Integer
Dim defaultColor As Integer

Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case Is = "$B$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 1")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case Is = "$C$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$D$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 2")
lowerLimit = 30
upperLimit = 40
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$E$1"
Set anyShape = Worksheets("Sheet1").Shapes("AutoShape 1")
lowerLimit = 130
upperLimit = 140
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents=True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If Target.Value < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf Target.Value > upperColor Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape=Nothing
End Sub


:

Hello again,

the 5 shapes would be on the same page. Each refercing a different cell,
say a1 to a5.

I tried replicating the code and changing the shape name in VB but that did
not work.

any thoughts?

Thanks



:

If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

:

Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub



:

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

:

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
"Pivot Man" wrote:

 

[Guest]

Would it be possible for you to send me a copy of your Excel file so I can
look at it and see what actual data and setup you have?

Since the code is now working without error, but results are "inconsistent",
that points to an error in our logic of testing values and setting things up.

You speak of only the <10 or >30 tests now, but earlier we had all kinds of
other things to think about: empty/blank cells, =0 cells, and potentially
others as non-numeric?

If you can send it, attach to emal to (remove spaces) HelpFrom @
jlathamsite.com and I'll beat on it some <g>

Hey, this is still not working. no longer getting errors, but colors do not
change consistently, nor does the limit seem to work.

ideally, the conditions would be

if <10 then Red
if >30 then Green
All else is Yellow

not sure if part of the problem is the limits.

I have checked the shape names and the cell references and they all seem to
be correct.
Thanks again.

mea culpa,
change the three definitions of the ...Color variables to type Long instead
of Integer; as
Dim lowerColor As Long
Dim upperColor as Long
Dim defaultColor as Long

Thanks again JLatham,

Unfortunately, I am getting an Runtime error '6' overflow error when I do
this. The code is stopping at the first uppercolor statement. Again, i
really appreciate your help.

:

Sure, we can use a special type of If Then, the Select Case to deal with
these multiple conditions. You should be able to modify it for your needs
pretty easily:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Integer ' or other type
Dim upperLimit As Integer ' or other type
Dim lowerColor As Integer
Dim upperColor As Integer
Dim defaultColor As Integer

Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case Is = "$B$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 1")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case Is = "$C$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$D$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 2")
lowerLimit = 30
upperLimit = 40
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$E$1"
Set anyShape = Worksheets("Sheet1").Shapes("AutoShape 1")
lowerLimit = 130
upperLimit = 140
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents=True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If Target.Value < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf Target.Value > upperColor Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape=Nothing
End Sub


:

Hello again,

the 5 shapes would be on the same page. Each refercing a different cell,
say a1 to a5.

I tried replicating the code and changing the shape name in VB but that did
not work.

any thoughts?

Thanks



:

If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

:

Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub



:

You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

:

This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:

Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:

Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:

I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

 

[Debra Dalgleish]

It may not meet your requirements, but another option is to use Wingding
font to create shapes, and conditional formatting for the three colours.
There's an example here:

http://www.contextures.com/xlCondFormat03.html#Shape

Hey, this is still not working. no longer getting errors, but colors do not
change consistently, nor does the limit seem to work.

ideally, the conditions would be

if <10 then Red
if >30 then Green
All else is Yellow

not sure if part of the problem is the limits.

I have checked the shape names and the cell references and they all seem to
be correct.
Thanks again.



:

mea culpa,
change the three definitions of the ...Color variables to type Long instead
of Integer; as
Dim lowerColor As Long
Dim upperColor as Long
Dim defaultColor as Long


:

Thanks again JLatham,

Unfortunately, I am getting an Runtime error '6' overflow error when I do
this. The code is stopping at the first uppercolor statement. Again, i
really appreciate your help.

:


Sure, we can use a special type of If Then, the Select Case to deal with
these multiple conditions. You should be able to modify it for your needs
pretty easily:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Integer ' or other type
Dim upperLimit As Integer ' or other type
Dim lowerColor As Integer
Dim upperColor As Integer
Dim defaultColor As Integer

Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case Is = "$B$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 1")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case Is = "$C$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$D$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 2")
lowerLimit = 30
upperLimit = 40
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$E$1"
Set anyShape = Worksheets("Sheet1").Shapes("AutoShape 1")
lowerLimit = 130
upperLimit = 140
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents=True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If Target.Value < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf Target.Value > upperColor Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape=Nothing
End Sub


:


Hello again,

the 5 shapes would be on the same page. Each refercing a different cell,
say a1 to a5.

I tried replicating the code and changing the shape name in VB but that did
not work.

any thoughts?

Thanks



:


If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

:


Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub



:


You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

:


This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:


Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:


Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:


I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

Dim cell As Range
Set cell = Selection

Dim value As Long
value = cell.value

' use whatever range you like here
If value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
Else
cir.Line.ForeColor.RGB = vbYellow
End If
End Sub

similarly, you can use the following macro to format the desired range.

Public Sub FormatRangeConditionally()
Dim value As Long

value = Range("A1")

With Range("B2:B4")
If value < 10 Then
.Interior.Color = vbRed
ElseIf value > 20 Then
.Interior.Color = vbGreen
Else
.Interior.Color = vbYellow
End If
End With
End Sub

Cheers,
socratis
:

 

[Guest]

Thanks Debra, it is an interesting solution.. but the goal was to have a
shape that I could overlay with the results, comparisson of actuals to budget
and have the overall shape change color around the results. But it is a very
simple alternative. I appreciate the response.

It may not meet your requirements, but another option is to use Wingding
font to create shapes, and conditional formatting for the three colours.
There's an example here:

http://www.contextures.com/xlCondFormat03.html#Shape

Hey, this is still not working. no longer getting errors, but colors do not
change consistently, nor does the limit seem to work.

ideally, the conditions would be

if <10 then Red
if >30 then Green
All else is Yellow

not sure if part of the problem is the limits.

I have checked the shape names and the cell references and they all seem to
be correct.
Thanks again.



:

mea culpa,
change the three definitions of the ...Color variables to type Long instead
of Integer; as
Dim lowerColor As Long
Dim upperColor as Long
Dim defaultColor as Long


:


Thanks again JLatham,

Unfortunately, I am getting an Runtime error '6' overflow error when I do
this. The code is stopping at the first uppercolor statement. Again, i
really appreciate your help.

:


Sure, we can use a special type of If Then, the Select Case to deal with
these multiple conditions. You should be able to modify it for your needs
pretty easily:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Integer ' or other type
Dim upperLimit As Integer ' or other type
Dim lowerColor As Integer
Dim upperColor As Integer
Dim defaultColor As Integer

Application.EnableEvents = False
Select Case Target.Address
Case Is = "$A$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case Is = "$B$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 1")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case Is = "$C$1"
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 10
upperLimit = 20
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$D$1"
Set anyShape = Worksheets("Sheet1").Shapes("Oval 2")
lowerLimit = 30
upperLimit = 40
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$E$1"
Set anyShape = Worksheets("Sheet1").Shapes("AutoShape 1")
lowerLimit = 130
upperLimit = 140
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents=True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If Target.Value < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf Target.Value > upperColor Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape=Nothing
End Sub


:


Hello again,

the 5 shapes would be on the same page. Each refercing a different cell,
say a1 to a5.

I tried replicating the code and changing the shape name in VB but that did
not work.

any thoughts?

Thanks



:


If each of these shapes is on a different worksheet (5 different sheets, 1
shape on each worksheet) then the exact same code will work for each
worksheet. Each sheet has it's own _Change() event! The only difference in
the code would be the name of the shape and any particulars about values and
the related color.

You can see this by choosing another worksheet and right-clicking on it's
tab and then choosing View Code. There will not be a Worksheet_Change()
event for that sheet. You can copy the original code, paste it in and make
changes unique to that particular sheet and shape. Excel will know which
sheet and shape on that sheet.

Is that clear? If not, let me know. Also, let me know if you are talking
about 5 different shapes on one sheet - in which case we'd have to really do
some changes to figure out which shape to work on based on the value entered
into A1.

:


Of course...stumbled on another question as I was working on this. If I want
to change multiple shapes conditionally, based on different criteria, I
assumed I could copy the code and replace the "oval 5" with the name of the
shape identified in the space above cell A1. So the question is, how do i
apply the code that worked so well to multiple shapes. Ultimately, the
parameters for each of the shapes and the reference cell A1, will be
different but I have left them the same in the code below.

Thanks again to both you and Socratis.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 3")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub


Private Sub W2orksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("square 1")
cir.Fill.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.Value < 10 Then
cir.Fill.ForeColor.RGB = vbRed
ElseIf Target.Value > 20 Then
cir.Fill.ForeColor.RGB = vbGreen
End If
Application.EnableEvents = True
End Sub



:


You're welcome. Glad I could help with the help that Socratis gave you to
begin with.

:


This was so clear and easy...Thank you for dumbing it down. It worked like a
charm. I actually changed the .line to .fill and that gave me exactly the
effect I was looking for. Again, thanks for all of your help.



:


Pivot Man,
I haven't tried his code, but looking at it, what it does is use the
currently selected cell as the one holding the value that controls the color
of the circle. So it would work like this: you click on the cell with the
control value in it and then use Tools | Macro | Macros to run this code.

To keep from having to choose the cell before running it, you could change
the Value = to specify the location of the value like (and I'd use something
other than the word value as the variable name, perhaps cirValue)
cirValue = Worksheets("Sheet1").Range("A1").Value
where Sheet1 is the name of the sheet and A1 is the cell with the value in it.

To be completely automated, you could move the code into the worksheet's
_Change event. That's easy enough to do: right-click on the worksheet's name
tab and choose [View Code] from the popup list. Then cut and paste this code
into the module, edit the sheet name and cell address as needed. Test it,
any time you make a change to the value in A1 (as coded) the circle should
react to the value change. Once you're sure it works properly, remove the
single apostrophe in front of the two 'Application.EnableEvents = statements
to make them active. That'll keep from continuously calling the routine if
lots of cells are changed at once, as with a massive delete, while it's
working on the circle's color.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cir As Shape

'change to address of cell with value in it
If Target.Address <> "$A$1" Then
Exit Sub ' no change in A1
End If
'Application.EnableEvents = False
' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbYellow ' default

' use whatever values you need here
If Target.value < 10 Then
cir.Line.ForeColor.RGB = vbRed
ElseIf Target.value > 20 Then
cir.Line.ForeColor.RGB = vbGreen
End If
'Application.EnableEvents = True
End Sub





:


Hi Socratis,

I am a newbee to the macro world. I tried to attach the macro to the shape
and it was not working. in the macros, do i need ot replace set cell =
Selection..change Selection to cell containing item of interest? Same
cell.value...overall, where do i refernce the cell which will drive the color
in this macro?

Thanks so much for your assistance.

:


I concur with JLatham, but if you want to be able to format an object
conditionally, you might want to use the following macro.

Public Sub FormatCircle()
Dim cir As Shape

' Assuming your shape (Oval 5) is on worksheet (Sheet1).
Set cir = Worksheets("Sheet1").Shapes("Oval 5")
cir.Line.ForeColor.RGB = vbRed

 

[Guest]

Lessons Learned (after examining the workbook and code that Pivot Man sent to
me)
#1 - VB is case sensitive! while most worksheet functions are not, VB is,
and it had some impact in testing results: "$c$3" is not the same as "$C$3"
in VB.
#2 - We must keep in mind that the Worksheet_Change() event is not triggered
by the recalculation of a worksheet formula! Pivot Man was testing for
changes in cells with formulas - we didn't know that, and hadn't asked. The
code now tests for changes made to cells that the formula is dependent upon,
which do get changes from the keyboard.
#3 - The devil is in the details like that, and sometimes we just don't ask
the right questions.

Here is the final code that actually does function:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim anyShape As Shape
Dim lowerLimit As Single ' need single for decimal
Dim upperLimit As Single ' need single for decimal
Dim lowerColor As Long
Dim upperColor As Long
Dim defaultColor As Long
Dim testValue As Single

Application.EnableEvents = False
Select Case Target.Address
Case "$A$3", "$B$3"
testValue = Range("C3").Value
Set anyShape = Worksheets("Sheet1").Shapes("Oval 3")
lowerLimit = 0.8
upperLimit = 0.95
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbYellow
Case "$A$4", "$B$4"
testValue = Range("C4").Value
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 2")
lowerLimit = 0.85
upperLimit = 0.9
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbCyan
Case "$A$5", "$B$5"
testValue = Range("C5").Value
Set anyShape = Worksheets("Sheet1").Shapes("Rectangle 3")
lowerLimit = 0.7
upperLimit = 0.9
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case "$A$6", "$B$6"
testValue = Range("C6").Value
Set anyShape = Worksheets("Sheet1").Shapes("Oval 4")
lowerLimit = 0.75
upperLimit = 0.9
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbMagenta
Case Is = "$A$7", "$B$7"
testValue = Range("C7").Value
Set anyShape = Worksheets("Sheet1").Shapes("Oval 5")
lowerLimit = 0.75
upperLimit = 0.9
lowerColor = vbRed
upperColor = vbGreen
defaultColor = vbBlack
Case Else
'if not one of your addresses
Application.EnableEvents = True
Exit Sub ' get out!
End Select
anyShape.Fill.ForeColor.RGB = defaultColor
If testValue < lowerLimit Then
anyShape.Fill.ForeColor.RGB = lowerColor
ElseIf testValue > upperLimit Then
anyShape.Fill.ForeColor.RGB = upperColor
End If
Application.EnableEvents = True
Set anyShape = Nothing
End Sub