Conditional Formatting Shapes

[grayson1231]

All,

I'm in need of help on how to conditional format shapes based on a cell reference.

For example, let's assume I have a shape titled "LF" - what kind of code would be used if I want the shape "LF" to color code based on the number in cell A1.

if A1 >= 10 then color shape should be red
if A1 <= 9 then the color shape should be yellow

Can VBA accomplish something like this automatically without having to click a form button of any sort? I've been learning and getting better with VBA but this one has me completely stumped! Please help if possible!

 

[Rick Rothstein]

Give this event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.Value < 10 Then
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbYellow
    Else
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbRed
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[grayson1231]

Give this event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.Value < 10 Then
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbYellow
    Else
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbRed
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Thank you very much for the help. I'm trying to add a third rule and can't seem to figure it out. Example:

If A1 >= 10, Red
If A1 <=9 and >=1, Yellow
If A1 <=0, Green

I've tried adding another line with the "If Target.Value" but it doesn't seem to work. Please Help! And thank you in advance!

 

[Rick Rothstein]

Thank you very much for the help. I'm trying to add a third rule and can't seem to figure it out. Example:

If A1 >= 10, Red
If A1 <=9 and >=1, Yellow
If A1 <=0, Green

I've tried adding another line with the "If Target.Value" but it doesn't seem to work. Please Help! And thank you in advance!

You should not simplify your question for us when you ask them initially... when you do, you will almost assuredly come back (as you did here) for help on how to add the extra conditions you neglected to mention in your original post. It also usually means extra work for the volunteers here who you are asking to help you. Give this modified event code a try (it replaces the one I gave you earlier)...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.Value >= 10 Then
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbRed
    ElseIf Target.Value <= 0 Then
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbGreen
    Else
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbYellow
    End If
  End If
End Sub

 

[grayson1231]

You should not simplify your question for us when you ask them initially... when you do, you will almost assuredly come back (as you did here) for help on how to add the extra conditions you neglected to mention in your original post. It also usually means extra work for the volunteers here who you are asking to help you. Give this modified event code a try (it replaces the one I gave you earlier)...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.Value >= 10 Then
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbRed
    ElseIf Target.Value <= 0 Then
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbGreen
    Else
      ActiveSheet.Shapes("LF").Fill.ForeColor.RGB = vbYellow
    End If
  End If
End Sub

Works great. Thanks for your help.

 

[Vickite]

Hi, sorry to jump on this thread but I am trying to do the same thing, but only need the first code for two colours. I want my shape to be red if the cell value is >14 and green if not. When I try the event code by Rick above with some changes to reflect the cell and shape name it won't work, it fails on the 4th line. This is the code I used:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
If Target.Value < 14 Then
ActiveSheet.Shapes("GP").Fill.ForeColor.RGB = vbGreen
Else
ActiveSheet.Shapes("GP").Fill.ForeColor.RGB = vbRed
End If
End If
End Sub

I am very new to VBA, do you know what the problem might be please? Many thanks!

 

[Rick Rothstein]

When I try the event code by Rick above with some changes to reflect the cell and shape name it won't work, it fails on the 4th line.

That is not very descriptive. Describe "won't work, it fails" for us. Are you getting an error message? If so, what is the error number and error description?

 

[My Aswer Is This]

This is a duplicate post Rick.http://www.mrexcel.com/forum/excel-questions/872470-conditional-formatting-shapes.html Vickite posted a similar post today and I asked a question and never received an answer.

That is not very descriptive. Describe "won't work, it fails" for us. Are you getting an error message? If so, what is the error number and error description?

 

[Vickite]

Guys I am in the UK, I was asleep! I have replied as soon as I woke up....

Sorry for not being descriptive, first time on the forum and first time trying this out. Rick I have tried to be more descriptive on the new thread I posted, sorry I wasn't sure if anyone would see this old thread and I need to get this done somehow over the weekend :-/

Many thanks for all your help