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