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

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$150
and set up your conditional shading. Change the values of 10 and 50 as
appropriate for the range in question.

"Pivot Man" wrote:

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

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:

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

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.

"Socratis" wrote:

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:

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

Thank you both...very much appreciated.

"JLatham" wrote:

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.

"Socratis" wrote:

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:

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

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.

"Socratis" wrote:

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:

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

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





"Pivot Man" wrote:

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.

"Socratis" wrote:

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:

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

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.



"JLatham" wrote:

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





"Pivot Man" wrote:

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.

"Socratis" wrote:

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:

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

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

"Pivot Man" wrote:

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.



"JLatham" wrote:

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





"Pivot Man" wrote:

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.

"Socratis" wrote:

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:

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

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

"Pivot Man" wrote:

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