Is there a way to create a custom Icon under conditional formatting. I am
trying to put a conditional format where if a number is between a certain
range it will assign a gold star to that cell. Is there any other way to do
this?

Thanks

Coolness,

No conditional formatting will do that, but you could use a macro: this assumes that the sheet has
goldstar shape named "GoldStar", and the gold stars are placed on cells in column E that are greater
than 95...

Sub PutGoldStars()
Dim myCell As Range
Dim myR As Range
Dim mySh As Shape

Set mySh = ActiveSheet.Shapes("GoldStar")
Set myR = Range("E1:E100")

For Each myCell In myR
If myCell.Value 95 Then
On Error Resume Next
ActiveSheet.Shapes("Star" & myCell.Address).Delete
mySh.Copy
myCell.Select
ActiveSheet.Paste
Selection.ShapeRange.Left = myCell.Left
Selection.ShapeRange.Top = myCell.Top
Selection.Name = "Star" & myCell.Address
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
Is there a way to create a custom Icon under conditional formatting. I am
trying to put a conditional format where if a number is between a certain
range it will assign a gold star to that cell. Is there any other way to do
this?

Thanks

I know I soud stupid, but how do I name a shape, so it knows what to put in
the cell? It doesn't recognize Gold Star. How can I create and name that
shape?

Thanks Again

"Bernie Deitrick" wrote:

Coolness,

No conditional formatting will do that, but you could use a macro: this assumes that the sheet has
goldstar shape named "GoldStar", and the gold stars are placed on cells in column E that are greater
than 95...

Sub PutGoldStars()
Dim myCell As Range
Dim myR As Range
Dim mySh As Shape

Set mySh = ActiveSheet.Shapes("GoldStar")
Set myR = Range("E1:E100")

For Each myCell In myR
If myCell.Value 95 Then
On Error Resume Next
ActiveSheet.Shapes("Star" & myCell.Address).Delete
mySh.Copy
myCell.Select
ActiveSheet.Paste
Selection.ShapeRange.Left = myCell.Left
Selection.ShapeRange.Top = myCell.Top
Selection.Name = "Star" & myCell.Address
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
Is there a way to create a custom Icon under conditional formatting. I am
trying to put a conditional format where if a number is between a certain
range it will assign a gold star to that cell. Is there any other way to do
this?

Thanks

Coolness,

Use the drawing toolbar to insert the star shape. Then, select the shape by clicking on it, and type
the name GoldStar into the name box above cell A1. When you first select the shape, the name box
will show the shape's original name, along the lines of "AutoShape 1"

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
I know I soud stupid, but how do I name a shape, so it knows what to put in
the cell? It doesn't recognize Gold Star. How can I create and name that
shape?

Thanks Again

"Bernie Deitrick" wrote:

Coolness,

No conditional formatting will do that, but you could use a macro: this assumes that the sheet
has
goldstar shape named "GoldStar", and the gold stars are placed on cells in column E that are
greater
than 95...

Sub PutGoldStars()
Dim myCell As Range
Dim myR As Range
Dim mySh As Shape

Set mySh = ActiveSheet.Shapes("GoldStar")
Set myR = Range("E1:E100")

For Each myCell In myR
If myCell.Value 95 Then
On Error Resume Next
ActiveSheet.Shapes("Star" & myCell.Address).Delete
mySh.Copy
myCell.Select
ActiveSheet.Paste
Selection.ShapeRange.Left = myCell.Left
Selection.ShapeRange.Top = myCell.Top
Selection.Name = "Star" & myCell.Address
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
Is there a way to create a custom Icon under conditional formatting. I am
trying to put a conditional format where if a number is between a certain
range it will assign a gold star to that cell. Is there any other way to do
this?

Thanks

Worked perfectly. Thanks very much!

"Bernie Deitrick" wrote:

Coolness,

Use the drawing toolbar to insert the star shape. Then, select the shape by clicking on it, and type
the name GoldStar into the name box above cell A1. When you first select the shape, the name box
will show the shape's original name, along the lines of "AutoShape 1"

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
I know I soud stupid, but how do I name a shape, so it knows what to put in
the cell? It doesn't recognize Gold Star. How can I create and name that
shape?

Thanks Again

"Bernie Deitrick" wrote:

Coolness,

No conditional formatting will do that, but you could use a macro: this assumes that the sheet
has
goldstar shape named "GoldStar", and the gold stars are placed on cells in column E that are
greater
than 95...

Sub PutGoldStars()
Dim myCell As Range
Dim myR As Range
Dim mySh As Shape

Set mySh = ActiveSheet.Shapes("GoldStar")
Set myR = Range("E1:E100")

For Each myCell In myR
If myCell.Value 95 Then
On Error Resume Next
ActiveSheet.Shapes("Star" & myCell.Address).Delete
mySh.Copy
myCell.Select
ActiveSheet.Paste
Selection.ShapeRange.Left = myCell.Left
Selection.ShapeRange.Top = myCell.Top
Selection.Name = "Star" & myCell.Address
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
Is there a way to create a custom Icon under conditional formatting. I am
trying to put a conditional format where if a number is between a certain
range it will assign a gold star to that cell. Is there any other way to do
this?

Thanks

I have one additional thing Im wondering if you can assist me with. What I'm
trying to do it set up a tracking chart where certain performance assigns a
different symbol, i.e. If someone beats both last years number and this years
goal, it will assign a GOLDSTAR in that cell. If they beat last years
number, but not this years goal, it will assign a black dot in that cell. If
they don't beat last years number it will assign a red dot. I will be adding
additional spreadsheets that contain that data, i.e. last years number, this
years goal and their actual number achieved. I am hoping that this macro can
look at that data and determine which symbol to assign. Will I need multiple
macros or will one take care of it? I tried to just copy the goldstar macro
formula again and named a different symbol with a different value, but it
gives me errors, so I don't know how to make the formula flow. Any
assistance would be greatly appreciated.

Thanks very much!

"Bernie Deitrick" wrote:

Coolness,

Use the drawing toolbar to insert the star shape. Then, select the shape by clicking on it, and type
the name GoldStar into the name box above cell A1. When you first select the shape, the name box
will show the shape's original name, along the lines of "AutoShape 1"

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
I know I soud stupid, but how do I name a shape, so it knows what to put in
the cell? It doesn't recognize Gold Star. How can I create and name that
shape?

Thanks Again

"Bernie Deitrick" wrote:

Coolness,

No conditional formatting will do that, but you could use a macro: this assumes that the sheet
has
goldstar shape named "GoldStar", and the gold stars are placed on cells in column E that are
greater
than 95...

Sub PutGoldStars()
Dim myCell As Range
Dim myR As Range
Dim mySh As Shape

Set mySh = ActiveSheet.Shapes("GoldStar")
Set myR = Range("E1:E100")

For Each myCell In myR
If myCell.Value 95 Then
On Error Resume Next
ActiveSheet.Shapes("Star" & myCell.Address).Delete
mySh.Copy
myCell.Select
ActiveSheet.Paste
Selection.ShapeRange.Left = myCell.Left
Selection.ShapeRange.Top = myCell.Top
Selection.Name = "Star" & myCell.Address
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
Is there a way to create a custom Icon under conditional formatting. I am
trying to put a conditional format where if a number is between a certain
range it will assign a gold star to that cell. Is there any other way to do
this?

Thanks

Coolness,

You don't really discuss worksheet structure, but below is an example where the symbol used is based
on values in two columns, C& D. (Assumes you have Goldstar, Silverstar, Bronzestar, and Blackdot
shapes on the activesheet.) Your comparison can be just about anything - the limits (95,90, etc)
can also be cells, either constant cells or cells on the same row as the cell needing the symbol.
If you need help with specifics, repost with specific info about where the values are relative to
the cell needing the symbol, and which comparisons are important.

HTH,
Bernie
MS Excel MVP

Sub PutStars()
Dim myCell As Range
Dim myR As Range
Dim mySh As Shape

Set myR = Range("C5:C18")

For Each myCell In myR
On Error Resume Next
ActiveSheet.Shapes("Symbol" & myCell.Address).Delete
If myCell.Value = 95 And myCell.Offset(0, 1).Value = 95 Then
Set mySh = ActiveSheet.Shapes("GoldStar")
mySh.Copy
GoTo PasteShape
End If
If myCell.Value = 95 And myCell.Offset(0, 1).Value = 90 Then
Set mySh = ActiveSheet.Shapes("Silverstar")
mySh.Copy
GoTo PasteShape
End If
If myCell.Value = 90 And myCell.Offset(0, 1).Value = 90 Then
Set mySh = ActiveSheet.Shapes("Bronzestar")
mySh.Copy
GoTo PasteShape
End If
If myCell.Value < 90 Or myCell.Offset(0, 1).Value < 90 Then
Set mySh = ActiveSheet.Shapes("Blackdot")
mySh.Copy
GoTo PasteShape
End If

PasteShape:
myCell.Select
ActiveSheet.Paste
Selection.ShapeRange.Left = myCell.Left
Selection.ShapeRange.Top = myCell.Top
Selection.Name = "Symbol" & myCell.Address
Next myCell
End Sub


"Coolness" wrote in message
...
I have one additional thing Im wondering if you can assist me with. What I'm
trying to do it set up a tracking chart where certain performance assigns a
different symbol, i.e. If someone beats both last years number and this years
goal, it will assign a GOLDSTAR in that cell. If they beat last years
number, but not this years goal, it will assign a black dot in that cell. If
they don't beat last years number it will assign a red dot. I will be adding
additional spreadsheets that contain that data, i.e. last years number, this
years goal and their actual number achieved. I am hoping that this macro can
look at that data and determine which symbol to assign. Will I need multiple
macros or will one take care of it? I tried to just copy the goldstar macro
formula again and named a different symbol with a different value, but it
gives me errors, so I don't know how to make the formula flow. Any
assistance would be greatly appreciated.

Thanks very much!

"Bernie Deitrick" wrote:

Coolness,

Use the drawing toolbar to insert the star shape. Then, select the shape by clicking on it, and
type
the name GoldStar into the name box above cell A1. When you first select the shape, the name box
will show the shape's original name, along the lines of "AutoShape 1"

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
I know I soud stupid, but how do I name a shape, so it knows what to put in
the cell? It doesn't recognize Gold Star. How can I create and name that
shape?

Thanks Again

"Bernie Deitrick" wrote:

Coolness,

No conditional formatting will do that, but you could use a macro: this assumes that the sheet
has
goldstar shape named "GoldStar", and the gold stars are placed on cells in column E that are
greater
than 95...

Sub PutGoldStars()
Dim myCell As Range
Dim myR As Range
Dim mySh As Shape

Set mySh = ActiveSheet.Shapes("GoldStar")
Set myR = Range("E1:E100")

For Each myCell In myR
If myCell.Value 95 Then
On Error Resume Next
ActiveSheet.Shapes("Star" & myCell.Address).Delete
mySh.Copy
myCell.Select
ActiveSheet.Paste
Selection.ShapeRange.Left = myCell.Left
Selection.ShapeRange.Top = myCell.Top
Selection.Name = "Star" & myCell.Address
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP


"Coolness" wrote in message
...
Is there a way to create a custom Icon under conditional formatting. I am
trying to put a conditional format where if a number is between a certain
range it will assign a gold star to that cell. Is there any other way to do
this?

Thanks