Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Returning a Garphic when certain criteria are met?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    London
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to get a graphic (i.e. red circle, yellow circle, green circle etc..) to appear when the output from a calculation is within a certain parameter i.e. red circle if between 0% and 25%. I'm happy with returning messages but not graphics.

    Can anyone help?

    Cheers

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Adam,

    just my 2 penneth-worth seeing as nobody's replied yet......

    could you return a lowercase "o" or an uppercase "O" when the condition(s) is/are met ?

    you said you're happy with returning comments so this should be quite easy

    then just format the font colour as green red or blue using conditional format

    if you need more than 3 colours, there are loads of VBA solutions to >3 colours via conditional formatting already on this board.... have a quick search on "conditional formatting"

    if nothing else, at least this post will flag you back to the top of the board and hopefully someone will see it who knows how to write code for conditional graphics


    :: Pharma Z - Family drugstore ::

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This can be done with some VBA, not that tough, but, need a little more details.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    This assumes you have a shape called Oval 1
    drawn on your sheet.
    Anyway this should get your foot in the door to doing what you are needing to do.

    If value entered in cell A2 is less than 25%
    will change the cirle to red
    Between 26-50% will make it yellow...

    Have Fun!



    [ This Message was edited by: TsTom on 2002-03-23 16:40 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You might need this...

    Private Sub Worksheet_Change(ByVal Target As Range)

    if target.row <> 2 or target.column <> 1 then exit sub

    Select Case Target.Value 'where target cell stores the result of the equation
    Case Is < 0.25
    With ActiveSheet.Shapes("Oval 1")
    .Fill.Visible = msoTrue
    .Fill.Solid
    .Fill.ForeColor.SchemeColor = 10
    .Fill.Transparency = 0#
    .Line.Weight = 0.75
    .Line.DashStyle = msoLineSolid
    .Line.Style = msoLineSingle
    .Line.Transparency = 0#
    .Line.Visible = msoTrue
    .Line.ForeColor.SchemeColor = 64
    .Line.BackColor.RGB = RGB(255, 255, 255)
    'change the color of the line if you wish
    End With
    Case 0.26 To 0.5
    With ActiveSheet.Shapes("Oval 1")
    .Fill.Visible = msoTrue
    .Fill.Solid
    .Fill.ForeColor.SchemeColor = 13
    .Fill.Transparency = 0#
    .Line.Weight = 0.75
    .Line.DashStyle = msoLineSolid
    .Line.Style = msoLineSingle
    .Line.Transparency = 0#
    .Line.Visible = msoTrue
    .Line.ForeColor.SchemeColor = 64
    .Line.BackColor.RGB = RGB(255, 255, 255)
    End With
    Case Else
    With ActiveSheet.Shapes("Oval 1")
    .Fill.Visible = msoFalse
    .Fill.Transparency = 0#
    .Line.Weight = 0.75
    .Line.DashStyle = msoLineSolid
    .Line.Style = msoLineSingle
    .Line.Transparency = 0#
    .Line.Visible = msoFalse
    End With
    End Select

    End Sub

    [ This Message was edited by: TsTom on 2002-03-23 16:38 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •